Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Seems like you are using variables with parameters... try this
Sum({<$1, [Item Code] = {"=Rank(Sum({<$1>} [PO Quantity]), 4) <= $(vTop)"}>} [PO Quantity])
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
Sunny,
It shows data, but doesn't cut data by Period
What exactly do you mean when you say cut data by period? Would you be able to elaborate on this using an example?
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
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.
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])
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])