Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I had asked for help on this before and got some great advice which got me close.
Here is a rough example of my data.
Case # LOS
1 18
1 18
1 18
2 7
3 9
3 9
So each case can have multiple rows. But the LOS will always be the same for each record for that case.
So I need to calculate the AVG LOS for the above group of cases data. So I would add (18+7+9)/3. I only want to add the LOS one time for each case.
I have gotten really close. But I have some situations where the calculation is not working correctly.
Any more advice would be greatly appreciated.
May be like this:
Avg(Aggr(Only(LOS), [Case #]))
Or can you just do Avg(Distinct LOS)
Case# Value
1 18
1 18
1 18
2 9
3 9
3 9
Avg(Distinct LOS) will give 13.5 (18+9)/2 where as the correct answer should be 12 (18+9+9)/3
I can't do that as cases can have the same LOS. So doing a Distinct LOS will not work if case 1 has 18 and case 2 has 18.
The average should be 18 (36/2).
Avg(Distinct LOS) will give me 9(18/2).
Is it possible to display a standard table that only shows the grand total line and none of the details in Qlik Sense
Have you tried this?
Avg(Aggr(Only(LOS), [Case #]))