Previous Week Amounts

AO3

Hello, the ultimate goal of my beastmode is to write a beastmode that will provide the variance week over week for total Net Cost. If you look at the attached picture, all I need is a beastmode that would produce the yellow column — the total Net Cost for the previous week. From there it'll be easy to then make a beastmode for the variance between the first two columns.

The dataset I'm working with has multiple rows for each Week Start Date. So to get Net Cost for a given week, you have to use a CASE WHEN to isolate that Week Start Date, then SUM the Net Cost.

We also have a useful column in the dataset called Cumulative Week #. It's just a constant for each different Week Start Date that counts up by 1 each week.

Appreciate any ideas for the most efficient way to do this. Thank you!

prev_wk_calc.png

GrantSmith

The issue is that you have multiple values for the same week so using a LAG window function won't work in your case. When I'm dealing with period over period analysis I'll utilize my own date dimension table and utilize beast modes to calculate the period over period (in your case week over week). You can read more on this here:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

AO3

@GrantSmith definitely thought about ETL work but the dataset is the company's biggest and I don't anticipate being able to make any additions there column-wise. So is there no way to do this with a Beastmode, without amending the dataset?

AO3

@GrantSmith is there any way to do this with a beastmode given the dataset structure laid out? Thank you for your help

Jones01

@AO3 can you post a small sample of the raw data please?