Beastmode for multiple row comparisons to one source row?

RocketMichael
RocketMichael Member
in Beast Mode

I'd like to add a third column in a table visualization that displays the % change for the current week to each previous period listed.


What's the best way to create a beast mode for this?

Screen Shot 2022-12-22 at 2.31.41 PM.png


Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @RocketMichael

    If those are your only records in your dataset you can utilize a Magic ETL to add a constant to your dataset (Join Column, value 1), then filter your data for just 'Current Week'. Take that filter and join it back to your original dataset with the constant and join both on the constant. This will join each row to the current week record for you to compare the two values together.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • travorhouse
    travorhouse Member

    I'd suggest creating a Magic ETL flow and using the Lead Window function.


    Here's how to do this:

    1. Connect the Window Function tile to your dataset in the Magic ETL.
    2. Here you will use the Lead function and will run the function on the column SCOGS with an offset of 1
    3. Next, you will order your function on the "Period" column and choose ascending.

    Now, you have the previous period on the same row as the period before so that you can perform the row-wise calculation needed to get the % change column you were looking for.


    Screen Shot 2022-12-25 at 11.37.37 PM.png


    Let me know if you have any further questions or need any additional help. Merry Christmas!

  • RocketMichael
    RocketMichael Member
    edited December 2022

    Hi Travor!

    Thanks for your reply and explanation. Apologies, I might not have explained this clearly enough. This sort of solution seems like the right path to me, though.

    I want to compare every period with the current week.

    So, in the example you created, the third column would read 222405 for every row.

    What's the best way to do that?

    (And an early Happy New Year to you!)

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @RocketMichael

    If those are your only records in your dataset you can utilize a Magic ETL to add a constant to your dataset (Join Column, value 1), then filter your data for just 'Current Week'. Take that filter and join it back to your original dataset with the constant and join both on the constant. This will join each row to the current week record for you to compare the two values together.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • RocketMichael
    RocketMichael Member

    @GrantSmith

    Brilliant! Thank you.

    I was making this too complicated trying to figure out a way to make a dynamic constant. This is perfect. 👌

Categories