KPI If statement

[bradlocke]

Hello, everyone. First, a quick thanks to all of the folks out there answering questions. These discussion boards have been very helpful.

On to my issue... I am trying to write an expression that will only show the KPI if a calculation is above a certain threshold, in this case 90%. The expression editor indicates my expression is OK but my KPI always shows 0; it never gets to the "else" part of the expression. I'm sure I have something completely wrong here. Any help would be greatly appreciated!

=Count(Aggr(If(Count(({<"THE NEED"={'Y'}>}) / Count({<"THE STATUS"={'Y'}>})) > 0.9), 0, Count(distinct{<"THE NEED"={'Y'}, "THE STATUS"={'Y'}>} "ID NUMBER")))

"THE NEED" field has multiple values, but I need to count the "Y" variables only. The same with "THE STATUS" field. "THE STATUS" field will always be greater than "THE NEED" field. Basically if the need is greater than 90% of the status then I want to show a zero in the KPI. If it is 90% or less, then I want the KPI to count the number of ID numbers, where the need and the status are both "Y". Hope this makes sense!

[sunny_talwar]

Oh yes, I see the problem.... it was all my bad... what dimension/s are you checking this (

Count({<[THE NEED] = {'Y'}>} [ID NUMBER])
    /
    Count({<[THE STATUS] = {'Y'}>} [ID NUMBER]) > 0.9

) against?

[jonathandienst · Accepted solution]

If this expression is just a KPI, then you may not need the Aggr(). How about this:

If(Count({<[THE NEED]={'Y'}>} Distinct [ID NUMBER]) / 
	Count({<[THE STATUS]={'Y'}>} Distinct [ID NUMBER]) <= 0.9, 
	Count({<[THE NEED]={'Y'}, [THE STATUS]={'Y'}>} Distinct [ID NUMBER]),
0)

or

If(Count({<[THE NEED]={'Y'}>} [ID NUMBER]) / 
	Count({<[THE STATUS]={'Y'}>} Distinct [ID NUMBER]) <= 0.9, 
	Count({<[THE NEED]={'Y'}, [THE STATUS]={'Y'}>} Distinct [ID NUMBER]),
0)

If you do need to do this calculation at a more granuler level (by one or more dimensions):

Sum(Aggr(
If(Count({<[THE NEED]={'Y'}>}  [ID NUMBER]) / 
	Count({<[THE STATUS]={'Y'}>}  [ID NUMBER]) <= 0.9, 
	Count({<[THE NEED]={'Y'}, [THE STATUS]={'Y'}>} Distinct [ID NUMBER]),
0), <your dimension>)

[bradlocke]

Thank you both for your help! I think the expression below has got this working for me. I will just need to remove the filter that relates to "THE NEED" specifically as, when I select the filter, the data filters to both "THE NEED" and "THE STATUS" causing the outcome to be 1. I tried using the {1} TOTAL function but could not get that to work properly. That is ok though, this has my project back on track! Thank you again.

If(Count({<[THE NEED]={'Y'}>} Distinct [ID NUMBER]) /
 Count({<[THE STATUS]={'Y'}>} Distinct [ID NUMBER]) <= 0.9,
 Count({<[THE NEED]={'Y'}, [THE STATUS]={'Y'}>} Distinct [ID NUMBER]),
'REDACTED DATA')