WoW Comparison BeastMode

ARutledge · April 25

I'm trying to make a card that will show me revenue that has been newly parked (week over week). Our data captures snapshots each week of the data so we can compare if there are changes week to week. This is the BeastMode we thought would work but it's not working properly:

Case WHEN (Snapshot Week Rank = 1 and Status/Stage != 'Parked Revenue') and (Snapshot Week Rank = 0 and Status/Stage = 'Parked Revenue') then 'Newly Parked' END

Are there any suggestions how to make this function properly or another Beastmode that would work better?

brycec · May 1

For anyone finding this thread after the fact, I got on a call with @ARutledge and was able to fix the issue by changing the following:

Some of the columns in the Beast Mode were accidentally strings instead of columns. They were enclosed in single quotes instead of backticks.
Program Name, Billing Entity, and Deliverable were previously added to the Partition By clause of the Lag window function but that wasn't unique enough. There was another column Billing Month New that needed to be added to the Partition By and Order By clauses as well as a column in the table.

brycec · April 25

In your Beast Mode, your condition has Snapshot Week Rank = 1 AND Snapshot Week Rank = 0, which cannot both be true at the same. The same is happening to the Status/Stage condition. For week over week, you can use a Period Over Period chart type, change the graph by period to week, and compare week over week. Alternatively, if you are trying to do it in a Beast Mode, you can use a Window or Fixed function.

ARutledge · April 28

Okay thank you. Could you help me write a new Beastmode that would work then? I've never used window or fixed function before. I want the card to only show me new programs that have been marked "parked revenue" this week compared to last week when they were not parked.

brycec · April 28

(CASE

  WHEN LAG(`Status/Stage`) OVER (PARTITION BY `<unique_identifier>` ORDER BY `Snapshot Date` ASC) != 'Parked Revenue' 
    AND `Stage/Status` = 'Parked Revenue' THEN 'Yes'
  ELSE 'No'
END)

Add this Beast Mode as a filter, and select Yes. Make sure to replace the unique_identifier field with whatever your unique identifier is in the DataSet. Then, have the y-axis value of the chart be a count of your unique identifier column.

ARutledge · April 28

I entered this formula:

(CASE
WHEN LAG(Status/Stage) OVER (PARTITION BY Program Name ORDER BY Snapshot Date ASC) != 'Parked Revenue'
AND Stage/Status = 'Parked Revenue' THEN 'Yes'
ELSE 'No'
END)

But I get this error: Calculation Error : A column in this calculation did not exist.

(screenshot attached)

Error.png

brycec · April 28

What is the date column for when your snapshot occurred called? I just put Snapshot Date as an assumption, but yours isn't called that probably.

ARutledge · April 29

We do have "Snapshot Date" in the dataset

Snapshot Date.png

brycec · April 29

Oh lol, I put Status/Stage then Stage/Status. Just adjust the second reference to be Status/Stage.

ARutledge · April 29

Ahh thank you! I should have caught that too. I was able to create the beast mode now. I did have to add another partition so modified the formula to this:

(CASE
WHEN LAG('status/stage') OVER (PARTITION BY 'Program Name','Deliverable' ORDER BY Snapshot Date ASC) != 'Parked Revenue'
AND 'status/stage' = 'Parked Revenue' THEN 'Yes'
ELSE 'No'
END)

But I parked some revenue yesterday (snapshot is taken every day) and it's not showing up as parked in my card now that I have this Beastmode entered and I filtered for not in "no" since there were no yes's to select.

brycec · April 29

There should be yes's to select. Sometimes with window functions you have to save the card so that the Beast Mode saves, then refresh and see if you can select them then. If they still aren't there, something else needs to be adjusted.

ARutledge · April 29

I saved the card and refreshed and there are still no yes's to select. I'm not sure what else I need to adjust.

brycec · April 29

What is your date range filtered to? Or do you have any other filters on the card that could be filtering the values you need out?

ARutledge · April 29

I don't have any other filters on the card.

Screenshot 2025-04-29 141810.png

brycec · April 29

Oh, you're using a pivot table? That might be the issue. They aggregate things a lot differently and I haven't verified this specific Beast Mode on one. Can you try using a mega table?

ARutledge · April 29

I switched to Megatable and it's still not working.

Screenshot 2025-04-29 144222.png

brycec · April 29

You would have to add Billing Entity to the partition if you want to have that in the table too. But, if that doesn't work, then maybe another solution is best. You could make a column in the DataFlow to use. And maybe you already have that and I missed it. What is the Snapshot Week Rank column?

ARutledge · April 29

I added 'Billing Entity' into the partition and it still didn't work. I do have 'Snapshot Week Rank' in my dataset. Where 0 is this week, 1 is last week, 2 is 2 weeks ago etc.

Screenshot 2025-04-29 154900.png

brycec · April 29

In your initial post, you said the data was snapshotted every week. But in a recent reply, you said the data was snapshotted every day. Which is it? I've been under the assumption that it was once a week, so if that isn't true, the Beast Mode would need to be updated to reflect that. There is likely a way to achieve it in the Beast Mode, but if it is every day it is probably just easier to achieve it in the DataFlow.

What you want is to have the Status/Stage from last week be on the same row as the Status/Stage from this week. That is what I was trying to have you achieve with the window function in the Beast Mode. In the DataFlow, the best way to do that is the Rank & Window tile, but in this case it could also be achieved with a join. For Rank & Window, you'd set it up the same as the Beast Mode. But first, you need to group the data by week. You can choose to join this afterward to the original data if you still want it to be by day. Then use the Lag function on the Status/Stage column and name it something like Previous Week Status/Stage, sort by Snapshot Date or Snapshot Week Rank and partition by your other columns. Then, in a Beast Mode in the card, you just need to compare where Previous Week Status/Stage != 'Parked Revenue' and Stage/Status = 'Parked Revenue' and filter to that.

ARutledge · April 30

I took another look at the data. Snapshots are taken once a week. (for example the latest snapshots were 4/26, 4/19, 4/12 etc) and then data for current day is also there. Is there a way to screenshare to walk me through this and try and figure it out together?

brycec · April 30

Sure, I'll DM you

brycec · May 1

For anyone finding this thread after the fact, I got on a call with @ARutledge and was able to fix the issue by changing the following:

Some of the columns in the Beast Mode were accidentally strings instead of columns. They were enclosed in single quotes instead of backticks.
Program Name, Billing Entity, and Deliverable were previously added to the Partition By clause of the Lag window function but that wasn't unique enough. There was another column Billing Month New that needed to be added to the Partition By and Order By clauses as well as a column in the table.

ARutledge · May 1

Thank you so much, Bryce! I do have one more question though, sorry! I only want my card to show newly parked revenue as of this week. I put "snapshot week rank" into the filters and selected 0 but then it got rid of all of the data even though there is newly parked revenue in snapshot week ran 0.

Snapshot Date.png

brycec · May 1

(CASE

  WHEN LAG(`Status/Stage`) OVER (PARTITION BY `Program Name`,`Deliverable`, `Billing Entity`, `Billing Month New` ORDER BY `Snapshot Date` ASC, `Billing Month New` ASC) != 'Parked Revenue'

AND `Status/Stage` = 'Parked Revenue' AND `Snapshot Week Rank` = 0 THEN 'Yes'

  ELSE 'No'

END)

You'll have to add it to the Beast Mode. When adding it to the card, it removes the previous week's rows, so the Lag function can't access them. But if it is a condition in the Beast Mode, the Lag still properly gets the previous week's row and then you filter to just Snapshot Week Rank of 0.

ARutledge · May 1

that worked perfect. thank you so much!!

WoW Comparison BeastMode

Best Answer

Answers

Categories