4 Replies Latest reply on Oct 14, 2018 5:15 PM by Jim Dehner

    Rolling 4 week calc based on selected week

    Natalie Lee

      Hi everyone,

       

      I've searched the forums and have tried multiple methods to try and get my rolling calc to work with no success so i've put together some sample data hoping someone from here is able to help.

       

      In the attached workbook, i'm trying to calculate a rolling 4 week calc based on the selected filter 'week of week ending' to achieve a 'rolling profit per unit' per employee for the last 4 weeks (inclusive of the one selected). What is the best formula to achieve this? The end goal is for it to be dynamic so we would be able to select a certain week of the year with the rolling calc updating automatically.

       

      The formula I need to use is '(profit 1 + profit 2)/units' and should be expressed as a %. Also, my dataset has hundreds of employees and 30+ weeks so it looks a little more complicated than the one in the sample.

       

      I've tried the following:

       

      • IF (DATEDIFF('week',[Week Ending],TODAY()))<=4 THEN [Profit 1] ELSE null END I was going to create a calculated field for each portion of the formula before aggregating them however it was only pulling 1 week's worth of results.
      • WINDOW_SUM(SUM([Profit 1),-3,0) wasn't computing correctly here but I don't feel a table calc is correct here unless I can include a date function in this so it picks up weeks?

       

      Desired result (if week 33 is selected):

      Profit 1: - Lisa: 850, - Anne: 700, - Sam: 850

      Profit 2: - Lisa: 0, - Anne: 20, - Sam: 0

      Units: - Lisa: 35, - Anne: 35, - Sam: 35

      Profit per unit: - Lisa: 2428%, - Anne: 2057%, - Sam: 2428%

       

      Appreciate any advice! Thank you.