1 Reply Latest reply on Aug 21, 2018 6:00 AM by Zhouyi Zhang

    FYTD/CYTD and YTD/ MTD

    Avantika Pathak

      Hello,

       

      I have 2 filters as below-

      1st Filter name: FYTD/CYTD, it has 2 values - Financial  and Celender

      2nd Filter name: YTD/MTD, it has 2 values Yearly and Monthly.

      Now, there can be 4 combinations using these 2 filters.  FYTD-YTD and FYTD-MTD, here user should see data from Apr onward (tilldate).

      CYTD-YTD and CYTD-MTD, here user should see data from Jan onward(till date)

      I have written following code in calculated field, however its not working.

      *************************************************************************************************************************

      IF [FYTD/CYTD]='Calender' AND [YTD/MTD]='Yearly' AND YEAR([Report Date])= YEAR(TODAY())

      THEN [Score]

       

      ELSEIF [FYTD/CYTD]='Calender' AND [YTD/MTD]='Monthly' AND MONTH([Report Date])= MONTH(TODAY())

      THEN [Score]

       

      ELSEIF [FYTD/CYTD]='Financial' AND [YTD/MTD]='Yearly' AND YEAR([Report Date])= YEAR(TODAY()) AND DATEPART('month',[Report Date])>=4

      THEN [Score]

       

      ELSEIF[FYTD/CYTD]='Financial' AND [YTD/MTD]='Yearly' AND YEAR([Report Date])= YEAR(TODAY())+1 AND DATEPART('month',[Report Date])<=3

      THEN [Score]

       

      ELSEIF [FYTD/CYTD]='Financial' AND [YTD/MTD]='Montly' AND YEAR([Report Date])= YEAR(TODAY())AND  MONTH([Report Date])= MONTH(TODAY())

      THEN [Score]

       

      Else 0

      END

      *****************************************************************************************************************************

       

      how can I achieve this?

       

      Thanks,

      Avi