Adding a New Column to Calculate Difference From Week to Week

SLam
SLam Member
in Charting

Hello,

I am trying to add an extra column that will calculate the difference in order volumes from week to week. See attached screenshot for details:

Week 1 -> Week 2 difference is 7394

Week 2 -> Week 3 difference is 53

Week 3 -> Week 4 difference is 452

Basically using the current week's number and calculating the difference between the # of orders and the prior week. I am not seeing a difference function available in Domo.

Is there a Domo tool that will do this, or will I need a beast mode?

Any help is greatly appreciated!

User: "Orders.jpg"


Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user077529

    Domo supports window functions (you need to have the feature turned on in your instance. Talk with your CSM if you don't have it enabled) which can get you the prior week's value using the LAG window function. The caveat to this is that it assumes there are no missing week in your dataset.

    LAG(SUM(`Orders`)) OVER (ORDER BY YEAR(`OpenedDate`) * 100 + WEEKOFYEAR(`OpenedDate`))

    Simply Put, order my rows by the year and date (some simple math so that it ends up being sorted in the correct way YYYYWW) and get the total from the prior week.


    To then get the difference you can then take the sum for the current week and subtract the value from the prior week.

    SUM(`Orders`) - LAG(SUM(`Orders`)) OVER (ORDER BY YEAR(`OpenedDate`) * 100 + WEEKOFYEAR(`OpenedDate`))


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @user07759 and @GrantSmith


    while lag() is a brilliant solution for getting this done, keep in mind that lag will get the previous row. IF THERE IS A GAP IN WEEKS you'll end up comparing week 7 with week 5. which ... depending on the business question may or may not be correct.


    If you want to calculate current week versus previous week, build an offset date calendar like in the video below, it'll consistently allow you compare current week to previous week.

    https://www.youtube.com/watch?v=CDKNOmKClms&t=468s

    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