Formula for Revenue

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
Hello.  I have a table that lists daily sales counts.  I've created a summary report that aggregates this information by month but now I'm trying to add in a Revenue field where it takes in the total count for the month and multiplies this by a percentage that is driven by a tier.  So if the total monthly count <=x, count*0.015.  

The problem I believe I'm having is that although my summary table aggregates the daily totals to monthly, the formula is still looking at the daily counts so is not registering monthly counts that reach the next level(s) of tiers in order to run the calculation correctly.  So everything is calculating based on the first tier as the daily counts do not meet or exceed the first tier.

How can I fix this so that my formula looks at the aggregate monthly totals as opposed to the daily totals?

Thanks!
Photo of Christine

Christine

  • 60 Points

Posted 7 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
In order to multiply a value, by an aggregated list, you will need to actually aggregate the data.  

By this I mean, you have to summarize the data to a "Months" table, rather then just a report, grouped by month.

Once you have all the data summarized to the respective months, then you can use a formula for tiers.

There are several examples on this forum of how to set up a 'months' table, but let us know if you get stuck.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
Christine,
Matthew is correct, but you will need to aggregate corresponding to how your summary report now groups.

For example, if you had Tiering by Customer Purchases by month, then you would need to have a table with a key field like

Customer number - YYYMM

The issue is how to initialize that table and how to maintain it going forward. 
The initial load can be be via a summary report.

The maintenance can be done via an Action to detect that the required Summary record does not yet exists.  Then have a safety net report daily report Subscription to see if somehow any are missing.
Photo of Christine

Christine

  • 60 Points
Thanks for the quick responses...  I'm going to try creating the "month" table and see where that gets me!