Compare month to month | Table

user046467
user046467 Member
in Charting

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

  • ST_Superman
    ST_Superman Domo Employee

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

    2021-04-22_11-00-34.png


    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.

    2021-04-22_10-56-06.png


    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
    ST_Superman Domo Employee
    edited April 2021

    You can view my solution tables and dataflows here:

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

  • user046467
    user046467 Member

    @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
    jaeW_at_Onyx Coach

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

    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"

Categories