13 Replies Latest reply on Jun 21, 2018 3:40 PM by Helen Wang

    How to write calculation for 2018 and QoQ YoY

    Helen Wang

      Hi All,

       

      When I display the data numbers on the first row, I can calculate the quarter over quarter ( QoQ: I compare one quarter against its previous one, eg: (2018 Q1)/(2017 Q4)-1 );

      year over year (YoY: I compare one quarter against its the same one but from the previous year. eg: (2018 Q1)/(2017 Q1)-1

       

      It looks like this:

       

      Before the QoQ and YoY is based on the numbers in the first line, so their calculation formulas looks like this: ( I created the quick table calculation, and then just drag it into the calculated field:

       

      $POS QoQ:

      (ZN(SUM([$ Net POS Actual])) - LOOKUP(ZN(SUM([$ Net POS Actual])), -1)) / ABS(LOOKUP(ZN(SUM([$ Net POS Actual])), -1))

       

      $POS YoY:

      (ZN(SUM([$ Net POS Actual])) - LOOKUP(ZN(SUM([$ Net POS Actual])), -1)) / ABS(LOOKUP(ZN(SUM([$ Net POS Actual])), -1))

       

       

       

       

       

       

       

      The formula is:

      IF str(DATEPART('year',[Fiscal Quarter] ))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) >'2'

      THEN [$POS YoY]

      END

       

      When I tried to apply this, it just won't work !

       

      Please help!!!

       

      Sorry I cannot share the data. I would love any advice on the code!!!