Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rittermd
Honored Contributor

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
MVP
MVP

Re: Need expression help

May be like this:

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

Not applicable

Re: Need expression help

Or can you just do Avg(Distinct LOS)

MVP
MVP

Re: Need expression help

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
Honored Contributor

Re: Need expression help

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
Honored Contributor

Re: Need expression help

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

MVP
MVP

Re: Need expression help

Have you tried this?

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