Beastmode for multiple row comparisons to one source row?

RocketMichael

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?

GrantSmith

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

travorhouse

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.

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

RocketMichael

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

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

RocketMichael

@GrantSmith

Brilliant! Thank you.

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