Compare month to month | Table
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 :)
Answers
-
@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.
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________2 -
You can view my solution tables and dataflows here:
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________0 -
@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.
0 -
@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.
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
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'
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有