Previous Week Amounts

AO3
AO3 Member
in Beast Mode

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

Answers

  • GrantSmith
    GrantSmith Coach

    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

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

    @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

    @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

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

Categories