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.
The way I'd suggest setting this up is with several tables:
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.
