Creating a Rolling 4Q Total

n____l____
n____l____ Member
in Magic ETL

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?

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    edited December 2023 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.

Answers

  • pauljames
    pauljames Contributor
    image.png

    @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!

  • n____l____
    n____l____ Member

    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
    marcel_luthi Coach
    edited December 2023 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.

Categories