1 Reply Latest reply on Sep 30, 2017 4:28 AM by Norbert Maijoor

    Calculate Average % of Variance

    Brent Clements

      Greetings!

       

      I am trying to calculate the average % of variance (complete and projected orders) over the course of 6 weeks. 

       

       

      Company

      Week #

      Orders

      WoW %

      ABC, INC

      35

      713

       

      36

      389

      -45%

      37

      359

      -8%

      38

      429

      19%

      39

      340

      -21%

      40

      545

      60%

      41

      430

      -21%

      42

      555

      29%

       

      I would like to capture the average WoW over this time period:

       

      Previous 2 weeks (Weeks 37 & 38)

      Current week (Week 39)

      Next 3 weeks (Weeks 40 & 41)

       

      Average variance is roughly 10%.  I would like to capture this by company in my dashboard.

       

      I do not have a workbook to share, but would appreciate any guidance!

        • 1. Re: Calculate Average % of Variance
          Norbert Maijoor

          Hi Brent,

           

          Not sure but find my approach as reference below and stored in attached workbook version 10.2 located in the original thread

           

           

          0. today: today()

           

          1. today (Week numbers)

           

          2. Distributed:

           

          if [Week #]-[today (Week numbers)]=0 then "Current Week"

          elseif ([Week #])-[today (Week numbers)]>=1 and  ([Week #])-[today (Week numbers)]<=2  then "Next 2 weeks"

          elseif ([Week #])-[today (Week numbers)]<=-1 and  ([Week #])-[today (Week numbers)]>=-2  then "Last 2 weeks" END

           

          FYI I selected "Next 2 weeks" based on available data

           

          Regards,

          Norbert