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

# Using Boolean filter value in the calculated field

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%.

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

_____________________________________________________________________________________________________

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]

• ###### 1. Re: Using Boolean filter value in the calculated field

Please let me know if I have not done a good job of explaining the scenario? Also the reason for me not including the actual dashboard is because of sensitivity of data.

• ###### 2. Re: Using Boolean filter value in the calculated field

Hi Harish, are you able to upload a packaged workbook to have a look at?

One thing that might simplify things is that you not need to use '= true' for a boolean field in an if statement, so [v_FY?] would be the same as [v_FY?]=TRUE and not([v_FY?] the same as [v_FY?]=FALSE.

• ###### 3. Re: Using Boolean filter value in the calculated field

Thank you for responding.

I used the formula as advised but still not resolved.

Actually my problem is - I have to find out if the data in the current sheet has all 12 months of data (FY) if yes then [CY Run Rate] else [CY].

I tried COUNTD([Month]) but got error "cannot mix aggregate and non-aggregate in if".

if you can help me how to use count the if function or a workaround to achieve the same would be really helpful.

SAMPLE WORKBOOK ATTACHED