Solved: Re: KPI If statement - Qlik Community

bradlocke · ‎2019-05-16

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!

jonathandienst · ‎2019-05-20

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

keshavkumar · ‎2019-05-16

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

vikramv · ‎2019-05-16

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 · ‎2019-05-20

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

sunny_talwar · ‎2019-05-20

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 · ‎2019-05-20

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 · ‎2019-05-20

Would you share a screenshot of your expression?

bradlocke · ‎2019-05-20

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 · ‎2019-05-20

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 · ‎2019-05-20

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