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.

pauljames

@n____l____ hey there! look into period over period charts. You can compare quarters as well.

n____l____

That allowed me to compare current quarter against previous, but not sum them and display rolling totals.

Example of what I'm attempting to do:

Period	Amount	Type	TTM
Q4-2021	7.15085	Recurring
Q1-2022	8.15914	Recurring
Q2-2022	15.32489	Recurring
Q3-2022	9.50269	Recurring	40.13757
Q4-2022	12.63425	Recurring	45.62097
Q1-2023	6.60336	Recurring	44.06519
Q2-2023	14.23964	Recurring	42.97994
Q3-2023	6.76756	Recurring	40.24481
Q4-2023	2.8588	Recurring	30.46936
Q4-2021	5.02187	Non-Recurring
Q1-2022	4.30219	Non-Recurring
Q2-2022	9.33729	Non-Recurring
Q3-2022	6.78918	Non-Recurring	25.45053
Q4-2022	6.20939	Non-Recurring	26.63805
Q1-2023	4.32506	Non-Recurring	26.66092
Q2-2023	7.09498	Non-Recurring	24.41861
Q3-2023	4.65316	Non-Recurring	22.28259
Q4-2023	2.15732	Non-Recurring	18.23052

I'm looking to turn the rolling 4q totals into 2 lines where the data points are from the TTM column. These were manually done in excel which is where I'm stuck currently.

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.