Rank and Window? Lag?

damen
damen Contributor
in Magic ETL

So I have a dataset that captures quarterly expense data.

I have been tasked with adding an 'expense ratio change column'

Basically, I need to know the % change of expenses changed from quarter to quarter by project

I know the math on this ((p2 - p1) / (p1)) but am not sure how to tile it in domo to make sure it is taking into account the 'project_number' (not shown)

Any suggestions? I need to create a column in the dataset btw, this is important

image.png

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen

    The Lag function should be used to add a column to hold the prior quarter expenses. In your first screenshot, name your new column "Previous Quarter Expenses" (or something similar), change the function drop-down to "Lag", run the function on your current quarter expense column, with an offset of 1.

    Once you've created this column, then I would add your variance calculation as a beast mode in your card like this:

    (sum(`Current Quarter Expenses`) - sum(`Previous Quarter Expenses`))/sum(`Previous Quarter Expenses`)

Answers

  • MichelleH
    MichelleH Coach

    @damen When you use the Rank and Tile column to find the expenses from the previous quarter, be sure to partition by project to make sure you are only including previous quarter data for the same project.

  • damen
    damen Contributor
    image.png image.png

    @MichelleH So here is my first attempt at creating a sequential column - this gave me that 'Latest Date' column above. I'm not sure where to go from here so that I can take expenses from ( (latest date 1) - (latest date 2) ) / latest date 2 and so on down the line (e.g. (p2-p3)/p3, (p3-p4/p4), etc

    If this helps, feel free to agree, accept or awesome it!

  • MichelleH
    MichelleH Coach
    Answer ✓

    @damen

    The Lag function should be used to add a column to hold the prior quarter expenses. In your first screenshot, name your new column "Previous Quarter Expenses" (or something similar), change the function drop-down to "Lag", run the function on your current quarter expense column, with an offset of 1.

    Once you've created this column, then I would add your variance calculation as a beast mode in your card like this:

    (sum(`Current Quarter Expenses`) - sum(`Previous Quarter Expenses`))/sum(`Previous Quarter Expenses`)

Categories