1 Reply Latest reply on Sep 29, 2016 4:00 PM by diego.medrano

    Current Week / Prior 4 Wk Avg - 1

    nigora.sharipova

      Hi,

       

      I am trying to calculate the variance for the following: sum([Metric X]) / [Prior 4Wk Avg - Metric X] - 1

      I am using the following formula to calculate[4Wk Avg - Metric X]:

      (

      lookup(sum([metric]),-24*7)+

      lookup(sum([metric]),-24*14)+

      lookup(sum([metric),-24*21)+

      lookup(sum([metric),-24*28)

      ) /4

       

      The data is displayed on an hourly basis for one day. Currently I am able to calculate it by hour, but i would like to display the total variance for the day as of the last available hour (Highlighted in red in the attached document is the numbers i would like display on the workbook).

       

      Your suggestions would be greatly appreciate.

       

      Thank you so much!