Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ranking - Top 20 Next 20

Hi

I have written a Sales Rep report that emails out a Top 20 Sales People graph in N Printing - Known as Division 1 Sales.

I am now being asked to create a Division 2 Sales table, how in Qlik is it possible to create a table looking at the Ranked Sales 21 - 40

I assume that Ranking has something to do with it but not sure how to use this function.

Any help would be gratefully received.

Thanks

Dave

24 Replies
Kushal_Chawda

modified the expression, check if it works,

=SUM({<CalendarMonth={"$(=vMonthCurrent)"},CalendarYear ={"$(=vYearCurrent)"},

[SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'},

[SageSaleRep]  = {"=Rank(SUM({<CalendarMonth={'$(=vMonthCurrent)'},CalendarYear ={'$(=vYearCurrent)'},

    [SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'}>}[GBR_Value]),0,1)>20 and

Rank(SUM({<CalendarMonth={'$(=vMonthCurrent)'},CalendarYear ={'$(=vYearCurrent)'},

    [SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'}>}[GBR_Value]),0,1)<=40"}>}[GBR_Value])

or

=SUM({<CalendarMonth={"$(vMonthCurrent)"},CalendarYear ={"$(vYearCurrent)"},

[SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'},

[SageSaleRep]  = {"=Rank(SUM({<CalendarMonth={'$(vMonthCurrent)'},CalendarYear ={'$(vYearCurrent)'},

    [SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'}>}[GBR_Value]),0,1)>20 and

Rank(SUM({<CalendarMonth={'$(vMonthCurrent)'},CalendarYear ={'$(vYearCurrent)'},

    [SageSaleRep] -={'Inter Company NS', 'Inter Company CC','Inter Company NS'}>}[GBR_Value]),0,1)<=40"}>}[GBR_Value])

Not applicable
Author

Hi Kurshal

Thanky ou so much that worked perfectly

Regards

Dave

Kushal_Chawda

Glad that it worked you can mark that response as helpful

patroser
Partner
Partner

So I did some testing on this.

I created a chart containing 3 expressions (for simulating a little bit more calculation time) which do basically the same, except for calculating based on another value field.

I compared the calculation time of the chart with the 3 intersections with the set combined by and terminating the QV instance after each calculation and even restarting my PC to be on the save side.

It seems that the "and"-solution works about two times faster than the intersection, therefore being more performant!

I will enrich my original answer with this finding.

Thanks for the input kush141087‌!

With just one expression the difference is not that big, but still "and" is about 1.5 times faster

Which leaves me with the question if there is any use case for intersection over "and" for two sets within set analysis...

Best regards,

Patrick

Kushal_Chawda

Thanks patrick for giving your time in testing and get back to us. I think you read the HIC's blogs on set analysis in which there should be some points regarding the same. If i will find anything I will post it.