Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
tresesco seems it is modified now, after marking answer as correct
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
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)
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!
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)
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
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?
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
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)
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