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

[vanand3535]

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?

[vanand3535]

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?

[Patrik_Lundblad]

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 !

[vanand3535]

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

[vanand3535]

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?

[Patrik_Lundblad]

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.

[vanand3535]

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

[hic · Accepted solution]

Unfortunately there is not only one way to calculate fractiles. I have encountered at least 4 different ways, and they don't always result in the same numbers.

In the Qlik Engine, we use the same algorithm as the one used by Excel called PERCENTILE.INC. Enter your data in Excel, and verify it there. It should be the same.

The algorithm you define is the same as the one called PERCENTILE.EXC in Excel.

Further, Standford University uses a third one, described in https://web.stanford.edu/class/archive/anthsci/anthsci192/anthsci192.1064/handouts/calculating%20per...

Finally, Tukey has defined an additional one described in Statistics: 2.4.1. Quantiles, Fractiles: Quartiles, Hinges, Quintiles, Octiles, Deciles and Percenti...

HIC