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: 
rittermd
Master
Master

Need expression help

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.

6 Replies
sunny_talwar

May be like this:

Avg(Aggr(Only(LOS), [Case #]))

Not applicable

Or can you just do Avg(Distinct LOS)

sunny_talwar

This would give incorrect result if case# 2 and 3 have same values for example.


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


rittermd
Master
Master
Author

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

rittermd
Master
Master
Author

Is it possible to display a standard table that only shows the grand total line and none of the details in Qlik Sense

sunny_talwar

Have you tried this?

Avg(Aggr(Only(LOS), [Case #]))