Median of top 5 values

[anriretief]

Hi all

I have two dimensions (Seisoen and Maand)

I want to calculate the median of the top five values 

I used the following expression but it doesn't seem to give the overall median for the top 5 values

if(

num(left(rank(aggr(sum(if([Seisoen Kort]>=vSeisoenBrake,[MM Daaglikse Syfers],
if([Seisoen Kort]<vSeisoenBrake,[MM Maand Syfers]))),Seisoen,Maand)),1),'0') <=5
and
(mid(rank(aggr(sum(if([Seisoen Kort]>=vSeisoenBrake,[MM Daaglikse Syfers],
if([Seisoen Kort]<vSeisoenBrake,[MM Maand Syfers]))),Seisoen,Maand)),2,1)='-'
or
trim(mid(rank(aggr(sum(if([Seisoen Kort]>=vSeisoenBrake,[MM Daaglikse Syfers],
if([Seisoen Kort]<vSeisoenBrake,[MM Maand Syfers]))),Seisoen,Maand)),2,1))=''), 

median(aggr(sum(if([Seisoen Kort]>=vSeisoenBrake,[MM Daaglikse Syfers],
if([Seisoen Kort]<vSeisoenBrake,[MM Maand Syfers]))),Seisoen,Maand))
)

Any help would be greatly appreciated 

Regards

Anri

[uacg0009 · Accepted solution]

Hi Anriretief,

I created a small sample, and use median() function to calculate the result, hope that's what you want.

You can change the column name in your dashboard.

Aiolos

[anriretief]

Your example seems to work I attached an excel file with a table The output of the output of my Qlikview table should look similar, with the exception of an extra column added for the median I want to calculate the median over the Seisoen field with regards to each month in the Maand field Thank you for the help you have provided so far

[uacg0009]

Do you mean you want to calculate the median by month?
I saw your excel, the result is by month, is it wrong?
You can try to add "Maand" in aggr function to calculate the value by month.
If what I understand is wrong, please tell me.
It's better to give a sample to show what you want.
Aiolos

[anriretief]

Thank you I am going to add the dimension and see what happens

[anriretief]

Attached is a table of what my data looks like I want to get the median of the top 5 "Seasons" (Seisoen field), but for every "Month" (Maand field) I used the expression you provided as follows: median(aggr(if(rank( total sum([MM Maand Syfers]),1,1)<=5,sum([MM Maand Syfers])),Seisoen,Maand)) It defenitly works in the example you gave I just don't know how to tweak it to work for me

[anriretief]

Sorry here is the file attached

[uacg0009]

Could you please tell me where you use the expression?
Q1: in a chart expression or a text like me?
Q2: the result is wrong? or the result is null?
Q3: Any selections you chose?
Acutally for your excel. I can not know it's correct or not I think...

[anriretief]

In a chart expression No selections It gives a couple of values that are way off but mostly I get null values

[uacg0009]

that's problem actually I think..
because I use it in a text object.
I think you need to try to add all dimension fields into the aggr function
if not work, try to add "nodistinct' in aggr
Aiolos