4 Replies Latest reply on Jun 28, 2018 4:50 PM by Peter Fakan

    How to automate my calculation formula by indexing to avoid hard coding every quarter

    Helen Wang

      HI all,

       

      I need to extract data for "Current Quarter -3" , "Current Quarter -2" , "Current Quarter -1" , "Current Quarter".

       

      Current quarter now is 2018 Q2, so my formula for a quarter up to date is:

       

       

      IF  YEAR([Fiscal Quarter])= 2018

      AND DATEPART('quarter',[Fiscal Quarter]) <=YEAR(TODAY())

      THEN [Ship + Cust Open Orders CRD]

      END

       

      This formula has automated my calculation. I don't need to hard code in year = 2018 and quarter = 2 and update my code every new quarter.

       

      But I don't know how to do this for other quarters:

       

      for example, for "current quarter - 3" , I have hard coded the formula has below: (for 2017 Q3):

       

      ZN(IF str(DATEPART('year',[Fiscal Quarter] ))= '2017' AND str(DATEPART('quarter',[Fiscal Quarter] )) ='3'

      THEN [Ship Final Act-Fcst]

      END)

       

       

      I want to update that above formula to the automated calculations like my Quarter to date formula. So that I don't need to manually input Year and Quarter every time.

       

      I cannot share my data but I have provided some sample data.

       

      I really look for any advice on the code. I don't want to hand change my year and quarter every new quarter, thank you so much!