AlanSulzer
Qrew Member
18 days ago

Monthly Budget Vs Cost Tickets

I have tickets that can cost either \$2 or \$68 and I currently have over 4K tickets throughout the months of Jan 2024 - April 2024. I have \$368K budget spread over a 21month duration. I need to show how much over or under for each month with respect to the budget.

2 Replies

• The way I'd suggest setting this up is with several tables:

1. Budget (1 record)
2. Monthly Allocation (21 records)
3. Sold Tickets (4k records)

The relationships are:

Budget (parent) < Monthly Allocation (child)
Monthly Allocation (parent) < Sold Tickets (child)

Every Monthly Allocation will be related to the 1 parent budget record

Each sold ticket will need to be related to the Month it was sold in. This can be done manually or with a Pipeline.

The Budget will have the total \$368k
The Monthly Allocations will have their part of the \$368k budget

A summary field on the Budget table will show the total allocated to the months and a formula can compare if the amount allocated is over, under, or equal to the total budget.

A summary field on each Monthly Allocation can show how much \$\$ has been sold from the tickets and a formula can compare and show how much over or under budget it is for the month.

Let me know if this helps 👍

-Sharon
QuickBaseJunkie.com

• Huge fan of QB Junky. Thank you for doing those.

I'll make this short and simple. I figured out using Queries how to achieve the Monthly budget. using a formula query I summed up the Months total cost per market so each row in that table per market had the same value for the month.

Summed Cost field formula:

var text QONE = "{16.EX.'"&[Matched Market]&"'}AND{9.GTE.'"&[LR Date - First of Month]&"'}AND{9.LTE.'"&[LR Date - Last Day of Month]&"'}";
SumValues(GetRecords(\$QONE),23)

Then in another formula field used a query that took that summed total and did =(368,000/12)-(Summed month field for cost). This got me my delta repeated for all rows. Then in a table summary report I used the Max function to pull in those values for the Market per Month crosstab report.  Thank you again for the guidance and sorry I couldn't give better information up front.