Creating a Rolling 4Q Total

n____l____

Hello,

I have a dataset with a list of all customers and their recurring/non-recurring recognized revenue by quarter. Our business has a tremendous amount of seasonlity so I'm trying to report a rolling 4q total.

How could I go about showing the sum of a quarter and the 3 trailing as well?

marcel_luthi

This can be extremely simple or complex depending on whether your cards need to be able to be responsive to filters or not, like breaking this by region, industry, customer, etc. Here's a summary of an ETL solution that could work (not the only one):

a) Create a list of the quarters you want to make available (leverage the DOMO calendar Dataset for this)

b) Get the list of all of the unique combinations you'll need your data to be able to be split by (in this case you need at least the type)

c) Do a FULL OUTER between these first 2 steps.

d) Group your Revenue Data Based on the columns you plan to keep (from above) and do the SUM of AARR, this will give you the AARR by Quarter.

e) Do a LEFT join between c) and d) so that you'll have entries for all possible combinations.

f) Use a Rank and Window Tile with 3 Lag functions to get the previous 3 quarters, since you did the join you have entries for every possible quarter even if blank.

g) Do the total of the 4 values to get the 4 quarters running total (with IFNULL statements to make sure to cover for the filled data for all combinations).

This should give you a dataset with what you need for the graph, which each quarter displaying the running total of 4 quarters rather than the value for its own. Keep in mind that for the first couple of quarters this number will be lower as the previous ones are not included, so you might want to make the list of quarters longer than what you might actually want to display at least by 3 quarters.

Hope this makes sense.