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