Top 3 by Group

[naishaansh]

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

[vinieme12]

Try below

=sum(aggr(if(Times>=max(TOTAL <GroupName> Times,3),Times),GroupName,Reason))

[edwin · Accepted solution]

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)

[naishaansh]

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 )

[vinieme12 · Accepted solution]

Just add the total keyword 

=sum(TOTAL <GroupName> aggr(if(Times>=max(TOTAL <GroupName> Times,3),Times),GroupName,Reason))

[naishaansh]

Thanks . This worked

[naishaansh]

Thanks a ton for this detailed answer. This solution worked. 

[naishaansh]

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