1 2 Previous Next 18 Replies Latest reply on Nov 20, 2017 12:18 PM by Diogo Braga

# EXCLUDE one dimension value from LOD FIXED Calc

I am hoping this can be solved without having to share the workbook.

Here is the challenge: I have the following working LOD calc, but it the calc shouldn't apply if Metric = 'FOODSERVICE.'

Any ideas? Thank you!

{FIXED [School Code], [Metric]: if [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Red' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Red' and SUM([17-18 Fall Agree & Strongly Agree]) <.6

or

SUM([17-18 Fall Disagree & Strongly Disagree]) > 0.1 then '⚑'

elseif [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Green' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Green' and SUM([17-18 Fall Agree & Strongly Agree]) >=.9

or

SUM([17-18 Fall Strongly Agree]) > 0.6 and SUM([17-18 Fall Disagree & Strongly Disagree]) <=.05 then ' '

else ' '

end}

• ###### 1. Re: EXCLUDE one dimension value from LOD FIXED Calc

Did you try creating a if else condition like below

if Metric = 'FOODSERVICE' then ( "whatever you like to do")

else ( " Your calculation" )

end

1 of 1 people found this helpful
• ###### 2. Re: EXCLUDE one dimension value from LOD FIXED Calc

Yes, got an error.

Expected type integer, found string. Result types from 'IF' expression must match.

• ###### 3. Re: EXCLUDE one dimension value from LOD FIXED Calc

the fixed calculation is returning string value so, if expression should return a string as well.

if Metric = 'FOODSERVICE.' then ( '0')

else ( {FIXED [School Code], [Metric]: if [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Red' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Red' and SUM([17-18 Fall Agree & Strongly Agree]) <.6

or

SUM([17-18 Fall Disagree & Strongly Disagree]) > 0.1 then '⚑'

elseif [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Green' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Green' and SUM([17-18 Fall Agree & Strongly Agree]) >=.9

or

SUM([17-18 Fall Strongly Agree]) > 0.6 and SUM([17-18 Fall Disagree & Strongly Disagree]) <=.05 then ' '

else ' '

end})

end

1 of 1 people found this helpful
• ###### 4. Re: EXCLUDE one dimension value from LOD FIXED Calc

Use Inverted Commas for 0

1 of 1 people found this helpful
• ###### 5. Re: EXCLUDE one dimension value from LOD FIXED Calc

Thank you! The calc is valid, but not working as expected.

'Food Service' is an alias, the actual value is FOODSERVICE (I triple checked)

Based on the calc, it should show 'No' not the flag. Correct?

• ###### 6. Re: EXCLUDE one dimension value from LOD FIXED Calc

Try changing FOODSERVICE  to 'Food Service' in if else condition

1 of 1 people found this helpful
• ###### 7. Re: EXCLUDE one dimension value from LOD FIXED Calc

You have FIXED Calc Taking Precedence. Check it

• ###### 8. Re: EXCLUDE one dimension value from LOD FIXED Calc

How do I check that Deepak Rai ?

• ###### 9. Re: EXCLUDE one dimension value from LOD FIXED Calc

Sir,

It is most Probably Precedence Issue of LOD , Since you now have resolved Calculation Error..Please Check each Step or Reframe Syntex

Thanks

Deepak

1 of 1 people found this helpful
• ###### 10. Re: EXCLUDE one dimension value from LOD FIXED Calc

Any suggestion is greatly appreciated.

• ###### 11. Re: EXCLUDE one dimension value from LOD FIXED Calc

Food Service is the alias. FOODSERVICE is the actual value.

• ###### 12. Re: EXCLUDE one dimension value from LOD FIXED Calc

Then I have to ask you for a packaged workbook if you can provide one.

1 of 1 people found this helpful
• ###### 13. Re: EXCLUDE one dimension value from LOD FIXED Calc

Diogo,

Can you try INCLUDE instead of FIXED?

Thanks

Deepak

1 of 1 people found this helpful
• ###### 14. Re: EXCLUDE one dimension value from LOD FIXED Calc

INCLUDE turned the calc into a measure...so I didn't work, but I studying INCLUDE and EXCLUDE now since I am not so familiar and it might help with the solution.

I included all the calc inside the LOD, now I get the famous 'cannot mix aggregate and non-aggregate.' How can I fix that?

{FIXED [School Code], [Metric]:

if [Metric]='FOODSERVICE' then 'No'

elseif [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Red' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Red' and SUM([17-18 Fall Agree & Strongly Agree]) <.6

or

SUM([17-18 Fall Disagree & Strongly Disagree]) > 0.1 then '⚑'

elseif [CF 17-18 16-17 Fall Strongly Agree Diff] = 'Green' and

[CF 17-18 Fall Strongly Agree Below CSUSA Avg] = 'Green' and SUM([17-18 Fall Agree & Strongly Agree]) >=.9

or

SUM([17-18 Fall Strongly Agree]) > 0.6 and SUM([17-18 Fall Disagree & Strongly Disagree]) <=.05 then ' '

else ' '

end}

1 2 Previous Next