Creating a Rolling 4Q Total
Hello,
I have a dataset with a list of all customers and their recurring/nonrecurring 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?
Best Answer

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.
0
Answers

@n____l____ hey there! look into period over period charts. You can compare quarters as well.
IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU! 😎😎😎
0 
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
Q42021
7.15085
Recurring
Q12022
8.15914
Recurring
Q22022
15.32489
Recurring
Q32022
9.50269
Recurring
40.13757
Q42022
12.63425
Recurring
45.62097
Q12023
6.60336
Recurring
44.06519
Q22023
14.23964
Recurring
42.97994
Q32023
6.76756
Recurring
40.24481
Q42023
2.8588
Recurring
30.46936
Q42021
5.02187
NonRecurring
Q12022
4.30219
NonRecurring
Q22022
9.33729
NonRecurring
Q32022
6.78918
NonRecurring
25.45053
Q42022
6.20939
NonRecurring
26.63805
Q12023
4.32506
NonRecurring
26.66092
Q22023
7.09498
NonRecurring
24.41861
Q32023
4.65316
NonRecurring
22.28259
Q42023
2.15732
NonRecurring
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.
0 
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.
0
Categories
 All Categories
 1.3K Product Ideas
 1.3K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 281 Workbench
 2 Cloud Amplifier
 4 Federated
 2.8K Transform
 83 SQL DataFlows
 541 Datasets
 2.2K Magic ETL
 3.2K Visualize
 2.3K Charting
 518 Beast Mode
 26 Variables
 555 Automate
 129 Apps
 408 APIs & Domo Developer
 18 Workflows
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 343 Distribute
 85 Domo Everywhere
 257 Scheduled Reports
 1 Software Integrations
 81 Manage
 80 Governance & Security
 2 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 78 Community Announcements
 4.8K Archive