Ranking - Top 20 Next 20

[#1]

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

[Kushal_Chawda]

tresesco seems it is modified now, after marking answer as correct

[patroser]

Hi,

please find attached the sample Rank Application.
If there is a problem with my calculation I'm not aware of it to be honest.
In my sample application the expression works properly.

Is there anything specific you think might be wrong in my set?

Regards,

Patrick

[Kushal_Chawda]

patroser‌ out of two below which will be the faster?

1) SUM({<Employee = {"=Rank(SUM(Salary))>20"} * {"=Rank(SUM(Salary))<=40"}>}Salary)

2) SUM({<Employee = {"=Rank(SUM(Salary))>20 and Rank(SUM(Salary))<=40"}>}Salary)

[patroser]

Ah, sorry!

Yes, this was a Typo because I tested it with greater 10 less 20 and forgot to change the second number too!

Sorry for this!

[Kushal_Chawda]

patroser

There was a typo in your expression when you posted before marked answer as correct, but you already modified it

SUM({<Employee = {"=Rank(SUM(Salary))>20"} * {"=Rank(SUM(Salary))<=20"}>}Salary)

[patroser]

tbh I'm not sure, I'd need to check on this if there are remarkable performance differences.

My primary focus in answering was an setanalysis alternative to the "if(rank..." due to if not performing well

I'll try to create a sample and check on this.

Regards

Patrick

[Kushal_Chawda]

I think, expression you have suggested will take more time to compute as you are individually evaluating two sets for different conditions and then performing intersection. I might be completely wrong but logically it is what I feel.

swuehl‌ can suggest on this?

[#9]

Thanks to everyone for your help and assistance, just one last bit of advise.

I need to add some additional filters into the expression, what is the best way to get the following set analysis into the Rank expression

sum({$<

CalendarMonth={$(vMonthCurrent)}

    ,CalendarYear ={$(vYearCurrent)}

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

>}[GBR_Value])

Thanks again

Dave

[Kushal_Chawda]

try this

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

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

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

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

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

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

[#11]

Hi Kushal

Thankyou

Afraid it didn't work. I changed to - as Salary & Employye are aren't fields in the data.

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

Cheers