
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
KPI If statement
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!
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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>)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try removing aggr (as the aggregation parameter is null ) and try like this....
=Count(If(Count({<"THE NEED"={'Y'}>}) / Count({<"THE STATUS"={'Y'}>})) > 0.9, 0, Count(distinct{<"THE NEED"={'Y'}, "THE STATUS"={'Y'}>} "ID NUMBER"))
Thanks.
Vikky.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Where us the name of the field in the if statement's count functions? May be you need this
=Sum(Aggr(
If(
Count({<[THE NEED] = {'Y'}>} [ID NUMBER])
/
Count({<[THE STATUS] = {'Y'}>} [ID NUMBER]) > 0.9, 0,
Count(DISTINCT {<[THE NEED] = {'Y'}, [THE STATUS] = {'Y'}>} [ID NUMBER]))
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Sunny.
It would not accept the expression unless I added a “)” after the 0.9…(perhaps I have added in the incorrect place). However, I still get 0 as the result no matter what I do.
I have experimented with the expression but I am not finding any luck.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you share a screenshot of your expression?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Attached. Please ignore the fact that the field names show up in green. I have to change them before I can post a screenshot on a public forum.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you copy and paste this
=Sum(Aggr(
If(
Count({<[THE NEED] = {'Y'}>} [ID NUMBER])
/
Count({<[THE STATUS] = {'Y'}>} [ID NUMBER]) > 0.9, 0,
Count(DISTINCT {<[THE NEED] = {'Y'}, [THE STATUS] = {'Y'}>} [ID NUMBER]))
))
and see if this works?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
OK. I copied and pasted. See attached new screenshot with error.

- « Previous Replies
-
- 1
- 2
- Next Replies »