7 Replies Latest reply: May 8, 2018 7:55 AM by Henric Cronström

# How to find Inter-Quartile range for specific value in Qlik Sense

Hi all,

I want to find the Inter-Quartile range for data(GAMES_MISSED) for 2012 - 2016.

Here I have attached the data

As of now I'm using the formula:-

fractile({<[SEASON-Year]={'2016'}>}NBA_GAMES_MISSED,0.25) AND fractile({<[SEASON-Year]={'2012'}>}NBA_GAMES_MISSED,0.25)

But I'm not getting the correct output

Please check and tell me how to do this?

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

As of now my expression is:-

fractile({<[SEASON]={'2012','2013','2014','2015','2016'}>}GAMES_MISSED,0.25)

For Inter-Quartile range:-

I will have to find 1st and 3rd quartile then I will have to subtract 3rd quartile - 1st quartile

But how can can I find 1st and 3rd quartile?

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

Hi Vivek,

Check the attached app to see if it helps you. What I would like to know is how you would aggregate your data? Since you have multiple values per year.

If you were to use sum then it would look like this

Fractile( total  Aggr( Sum({<[SEASON]={'2012','2013','2014','2015','2016'}>}GAMES_MISSED),  [SEASON] ) ,0.75 )

-

Fractile( total  Aggr( Sum({<[SEASON]={'2012','2013','2014','2015','2016'}>}GAMES_MISSED) , [SEASON] ) ,0.25 )

Best regards,

Patrik !

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

Manually I tried to find but I'm getting 63.5 as Inter-quartile-range

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

Hi Vivek,

If you want all values in your calculation and not the aggregated ones then you're going to have to add in a new columns, such as record or row number and use in your calculation.

With 'record' the modified expression would be

Fractile( total  Aggr( Sum({<[SEASON]={'2012','2013','2014','2015','2016'}>}GAMES_MISSED), [Record] ) ,0.75 )

-

Fractile( total  Aggr( Sum({<[SEASON]={'2012','2013','2014','2015','2016'}>}GAMES_MISSED), [Record] ) ,0.25 )

Which ends up at 49.5 for me.

Also, I believe Fractile/Percentile in both Qlik as well as Excel is calculated slightly differently as to how you do it manually. Try it in excel and see what they calculate is as.

Attached an updated app.

Best regards,

Patrik.

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

I tried this:-

Fractile({<[SEASON-Year]={'2012','2013','2014','2015','2016'}>}NBA_GAMES_MISSED,0.75 )

-

Fractile({<[SEASON-Year]={'2012','2013','2014','2015','2016'}>}NBA_GAMES_MISSED,0.25 )

I'm also getting 49.5

• ###### Re: How to find Inter-Quartile range for specific value in Qlik Sense

I'm getting different different answer of q1 , q3  and q3-q1 by calculating manually and by qlik using expression.

I have attached the screen shots how I have calculated manually please check this tell me

is this correct or not?