Adding a New Column to Calculate Difference From Week to Week

SLam

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!

GrantSmith

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`))

jaeW_at_Onyx

@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