Calculate median of a limited table size

[ankitSiemens]

Hi All.

I have a data of 20 sales, sorted on basis of "Date and Time". I am displaying top 5 of these in a Qlik Table chart. To do this, I have set a dimension limit on the table, so that it displays only top 5, sorted by date and time.

Now, when I use the formula Median(Sale), then the median is being calculated of entire data, i.e. of 20 records, and not of the 5 rows present in the table. I want the median to be calculated only for these 5 items, that are visible to the user.

I am also displaying a KPI below, there too, I want the median to be calculated only for these 5 items.

I have attached a sample data below in excel.

[brunobertels]

Hi 

Try this 

median({<[Sale ID]={"=rank(sum([Sale (in Lakhs)]))<=5"} >}[Sale (in Lakhs)])

Regards 

[ankitSiemens]

The problem here is that the data will not be sorted based on sale. So, we won't be knowing that the top 5 records, all would have the sum less than 5.

[brunobertels]

Hi 

Not sure to understand your need 

is it : base on 20 rows you filter the 5 most recent date ? Or Sales under 5 or What ? 

Can you provide based on your sample the desired out put with wich Darte or SaleID  selected ? 

if it is on 5 most recent date : 

median({<[Date and Time]={"=aggr(rank([Date and Time]),[Date and Time])<=5"} >}[Sale (in Lakhs)])

if it is on Sales =< 5 : 

median({<[Sale ID]={"=sum([Sale (in Lakhs)])<=5"} >}[Sale (in Lakhs)])

[Kushal_Chawda]

@ankitSiemens  If I understood it correctly try below

If you have one line for one Date and Time then try below
=median({<[Date and Time]={">=$(=max([Date and Time],5))"}>}[Sale (in Lakhs)])

If you have multiple line fr one date and time then try below
=median(aggr(sum({<[Date and Time]={">=$(=max([Date and Time],5))"}>}[Sale (in Lakhs)]),[Date and Time]))