Rank function in set analysis

[vitaliichupryna]

Hi, 

I have a request to add straight tables with Top Items, it should be dynamical. Client want to have a possibility to switch between different measures and change numbers of top items. For this I use following calculation:

Sum({<[Item Code]= {"=rank(SUM([PO Quantity]), 4)<= $(vTop)"}>} [PO Quantity])

It shows Top Item Codes by PO Quantity and works fine with all data.

In the same time I have periods in my application - MTD, QTD, YTD and I need to show top items by different periods, so my formula should looks like:

Sum({<$($1), [Item Code]= {"=rank(SUM({<$($1)>} [PO Quantity]), 4)<= $(vTop)"}>} [PO Quantity])

where $($1) it's my period ex.: $(ePO_Quanity(YTD))

For some reason it doesn't work and I get null value instead of data.

Do you have any ideas about this issue?

Thanks,

Vitalii

[sunny_talwar]

Seems like you are using variables with parameters... try this

Sum({<$1, [Item Code] = {"=Rank(Sum({<$1>} [PO Quantity]), 4) <= $(vTop)"}>} [PO Quantity])

[pablolabbe]

Hi Vitali,

Please remember to post questions related to the product in the correct discussion board. 

Here is the list of discussion board for qlik products,  https://community.qlik.com/t5/Qlik-Products/ct-p/qlik-products

This post will be moved to the correct discussion board soon.

Thanks, Pablo

[vitaliichupryna]

Sunny,

It shows data, but doesn't cut data by Period

[sunny_talwar]

What exactly do you mean when you say cut data by period? Would you be able to elaborate on this using an example?

[vitaliichupryna]

I mean that period is my Data range and I have variables with period,QTD:

Year=, Month=, Quarter=, [Date]=, DayOfQuarter = {"<=$(=Max(DayOfQuarter))"},QuarterIndex = {"$(=Max(QuarterIndex))"}

So, I expect to see top 10 or top 7 from this data range, and for this reason I use parameters $($1) with Period

Thanks,

Vitalii

[sunny_talwar]

So, the YTD/QTD in the above examples are not field but some sort of expressions which include commas in them... unfortunately variable with parameters won't be able to handle this. 

[vitaliichupryna]

Yes, it doesn't work.

I decided to use following approach - select top items in the calculated dimension:

=Aggr(if(rank($(ePO_Quantity($(vPeriod))), 4)<=vTop ,[Item Code]), [Item Code])

[marcus_malinow]

I think your original approach may well work

In the attached, I have variables

vSales = SUM({$<$(=$1)>} Sales)

YTD = Year={2018}, Date={">=01/01/2018"}

QTD = Year={2018}, Date={">=01/10/2018"}

MTD = Year={2018}, Date={">=01/11/2018"}

I am now able to define expressions such as $(vSales(YTD)), $(vSales(QTD)), $(vSales(MTD)), and these work just fine.

In your example this would be equivalent to

Sum({<$(=$1), [Item Code]= {"=rank(SUM({<$(=$1)>} [PO Quantity]), 4)<= $(vTop)"}>} [PO Quantity])