Previous Week Amounts

Options
AO3
AO3 Member

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!

Answers

  • GrantSmith
    Options

    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:

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

    @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
    AO3 Member
    Options

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

  • Jones01
    Jones01 Contributor
    Options

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