Forum Discussion

ChristinePratt1's avatar
ChristinePratt1
Qrew Cadet
7 years ago

Formula for Revenue

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!
App builders
Formulas and functions
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    7 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • ChristinePratt1's avatar
    ChristinePratt1
    Qrew Cadet
    7 years ago
    Thanks for the quick responses...  I'm going to try creating the "month" table and see where that gets me!