topic Re: Rank function in set analysis in QlikView

Rank function in set analysis

vitaliichupryna — Sat, 16 Nov 2024 05:12:10 GMT

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

Re: Rank function in set analysis

sunny_talwar — Mon, 19 Nov 2018 12:48:16 GMT

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

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

Re: Rank function in set analysis

pablolabbe — Mon, 19 Nov 2018 12:48:44 GMT

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

Re: Rank function in set analysis

vitaliichupryna — Mon, 19 Nov 2018 13:04:57 GMT

Sunny,

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

Re: Rank function in set analysis

sunny_talwar — Mon, 19 Nov 2018 13:11:01 GMT

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

Re: Rank function in set analysis

vitaliichupryna — Mon, 19 Nov 2018 13:45:35 GMT

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

Re: Rank function in set analysis

sunny_talwar — Mon, 19 Nov 2018 13:50:28 GMT

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.

Re: Rank function in set analysis

vitaliichupryna — Mon, 19 Nov 2018 14:09:55 GMT

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])

Re: Rank function in set analysis

marcus_malinow — Mon, 19 Nov 2018 14:37:35 GMT

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])