Forum Discussion

AlanSulzer's avatar
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

    • AlanSulzer's avatar
      AlanSulzer
      Qrew Member

      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.