Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harisin22
Contributor II
Contributor II

Calculate median, IQR, and min/max from count by group

Hello:

I would like to calculate median, the IQR (25%, 75%), and min and max  of a count of values, by group. See example table below:

Input Table
GroupID_Unique
A1
A2
A+B3
B4
C+A5

 

The desired output will be in pivot table, structure as follows:

Desired Output     
GroupCount(ID_Unique)Median(Count(ID_Unique))Fractile(Count(ID_Unique), 0.25)Fractile(Count(ID_Unique), 0.75)Mix(Count(ID_Unique))Max(Count(ID_Unique))
A2     
B1     
A+B1     
C+A1     

 

I have spent a lot of time trying to figure how to calculate median/fractile (IQR) values from a count - I can't seem to figure this out.

Any help would be greatly appreciated.

Thank you,

Sin

Labels (2)
1 Reply
Oleg_Troyansky
MVP & Luminary
MVP & Luminary

Hi,

what you are trying to do is called "nested aggregation" - you are trying to apply one aggregated function on top of another. In Qlik Sense, you have to use the AGGR() function or the TOTAL qualifier in order to nest aggregation functions. For example:

median(

     AGGR(

          count(ID), Group)

)

I'm teaching a whole 3-hours lecture on the rules and analytical uses of the AGGR() function at the Masters Summit for Qlik - check out our agenda and see if our advanced training is for you. We will be in Washington, DC and in Amsterdam, Netherlands this fall.

Cheers,

Oleg Troyansky