Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bradlocke
Contributor III
Contributor III

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!

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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>)

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

12 Replies
keshavkumar
Contributor II
Contributor II

Try checking the brackets in if condition and also mention the dimension based on which you want it to aggregate.
vikramv
Creator III
Creator III

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.

 

 

 

bradlocke
Contributor III
Contributor III
Author

Thank you. I get an error - "Nest aggregation not allowed.".
sunny_talwar

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]))
)
bradlocke
Contributor III
Contributor III
Author

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.

sunny_talwar

Would you share a screenshot of your expression?

bradlocke
Contributor III
Contributor III
Author

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.

sunny_talwar

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? 

bradlocke
Contributor III
Contributor III
Author

OK. I copied and pasted. See attached new screenshot with error.