Compare month to month | Table

user046467

Hi all,

I am trying to get a table to show the difference between this month and the month prior:

January | 100 |

February | 50 | -50%

April | 200 | +400%

But how? This should be possible via beastmode but I can't figure it out.

I tried the month over month charts but they also don't show what I want so I want to try it within a table.

Hopefully someone can help me with the seemingly easy question.

Thanks in advance :)

ST_Superman

@user046467 Your table confused me a bit. If you want to show going from 100 to 50 as -50%, then I would be looking at the percent change from prior month. However, if you want to show going from 50 to 200 as +400%, then I would need to show the percent of the prior value, not the percent change (which would be 300%).

Either way, the challenge here was getting the lag function to work. I ended up using Magic ETL to solve for this. You can use the "Rank & Window" tile to grab the prior value.

I then Calculated the % change and the % of prior values using the differences between value and lag_value.

I ran into an issue when I noticed that your provided data was missing a month. To cover that possibility, I actually calculated two lag values, one offset by 1 and one offset by 2. I then created a "Calculated Lag Value" which was just IFNULL(Lag1 , Lag2 ) and found the % changes from that value.

ST_Superman

You can view my solution tables and dataflows here:

https://domo-dojo.domo.com/page/1777803864

user046467

@ST_-Superman-_ thanks for your detailed response!

How do I open the url you provided? When I try to login it says wrong credentials. Which make sense considering it isn't my domo url.

jaeW_at_Onyx

@ST_-Superman-_ we would have to add the user to domo-dojo. @user046467 if you haven't been added yet please email me your name and email.

jae@onyxreporting.com

that said, i try to avoid pre-aggregating data as Superman has done in ETL because it your card unable to respond to filters because the values are hard-coded.

What you're tring to accompolish can be done with Window functions, specfically the LAG() function.

@GrantSmith covered it in his top 10 beast modes video

https://www.youtube.com/watch?v=gO8OLpsAk4M&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=7&t=48s

And I covered a permutation of lag here:

https://www.youtube.com/watch?v=cnc6gMKZ9R8

Make sure to contact your CSM and ask them to enable 'window functions in beast modes'