3 Replies Latest reply on Feb 3, 2017 8:02 PM by Harish Balachandrappa

    Using Boolean filter value in the calculated field

    Harish Balachandrappa

      Case - I have 4 sheets with a date range filtered for Full Year (FY) / Month to Date (MTD) / Quarter to Date (QTD) / Year to Date (YTD), I have multiple calculated fields against each of the values in the dimension to compare with each other to show variance and Var%.

      Capture.PNG

      My current version or versus versions are selected through a drop down (below is the formula)

      _____________________________________________________________________________________________________

       

      Filter used in the FY - sheet is

       

      [v_FY?]=

      DATEPART('year',[Date])=DATEPART('year',[Select Month])

      or

      DATEPART('year',[Date])=datepart('year',DATEADD('year',-1,[Select Month]))

      _____________________________________________________________________________________________________

       

      IF [Parameters].[Current Version]='Plan' THEN [AOP]

      ELSEIF [Parameters].[Current Version]='PY' THEN [PY]

      ELSEIF [Parameters].[Current Version]="Target" THEN [AOP]

      ELSEIF [Parameters].[Current Version]="Actual" THEN [CY]

      ELSEIF ([Parameters].[Current Version]="CY run rate" AND [v_FY?]=TRUE) then [CY Run Rate]

      ELSEIF ([Parameters].[Current Version]="CY run rate" AND [v_FY?]=FALSE) then [CY]                                                  (THIS IS NOT WORKING)

      END

      _____________________________________________________________________________________________________

       

      Ask/question - I would like to have 2 different actions when when [Parameters].[Current Version]="CY run rate" is selected

                                sheet FY should show [CY Run Rate]

                                rest of the sheets should show [CY]

       

                               OR

       

                               What should be the logical formula to find out the Full Year sheet? so that I can use if Fill Year then [CY Run Rate] else [CY]