Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to display the groups and their failure rate like this
Group Name Failure Rate
Group 1 1.5%
Group 2 2.5%
The challenge is calculating failure rate , it has numerator and denominator.
I am able to calculate the denominator , no issues there. But I have challenges in calculating the numerator.
The logic to calculate numerator is : count of 'statuses' where 'status' = fail and is one of top 3 reasons for failed status within that Group.
The raw data looks like this so I need to find the top 3 reasons by summing the 'Times' column by Group and the display the total in the numerator. My numerator should be 230. Likewise I need to display for other groups too
Group Name | Status | Reason | Times |
Group 1 | Fail | Motherboard failure | 100 |
Group 1 | Fail | RAM Error | 80 |
Group 1 | Fail | Screen Crash | 50 |
Group 1 | Fail | Cracked screen | 20 |
Group 1 | Fail | Faulty hardware | 10 |
how about this one:
this is your numerator:
=if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,sum(Times))
this is your denominator:
=aggr(nodistinct sum( if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,Times)), [Group Name])
if you simply add reason, you will see reasons that does not meet the top 3 criteria with measures = null
so instead of just reason, use this as Reason dimension, + uncheck include null:
=if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,Reason)
Just add the total keyword
=sum(TOTAL <GroupName> aggr(if(Times>=max(TOTAL <GroupName> Times,3),Times),GroupName,Reason))
Try below
=sum(aggr(if(Times>=max(TOTAL <GroupName> Times,3),Times),GroupName,Reason))
how about this one:
this is your numerator:
=if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,sum(Times))
this is your denominator:
=aggr(nodistinct sum( if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,Times)), [Group Name])
if you simply add reason, you will see reasons that does not meet the top 3 criteria with measures = null
so instead of just reason, use this as Reason dimension, + uncheck include null:
=if(aggr(nodistinct rank(sum(Times)), [Group Name], Reason)<=3,Reason)
Thanks Vineeth. I tried your solution.
However it gives me this -
What I need is 100+80+50 = 230. I need 230 to be displayed (i.e. top 3 sum by Group )
Just add the total keyword
=sum(TOTAL <GroupName> aggr(if(Times>=max(TOTAL <GroupName> Times,3),Times),GroupName,Reason))
Thanks . This worked
Thanks a ton for this detailed answer. This solution worked.
I came across a data issue now. They need sum of top 3. The answer I am expecting is 200.
But when I consider your solution then it gives me 220 ( 100+80+20+20). It adds top 4 because of same ranking. They don't want 220 .
How do I overcome this? I think running count can help me overcome but it looks there is no native running count function in QlikSense.
Group Name | Status | Reason | Times |
Group 1 | Fail | Motherboard failure | 100 |
Group 1 | Fail | RAM Error | 80 |
Group 1 | Fail | RAM Error | 20 |
Group 1 | Fail | Cracked screen | 20 |
Group 1 | Fail | Faulty hardware | 10 |