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

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

patroser
Partner
Partner

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
Partner
Partner

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
Partner
Partner

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?

Not applicable
Author

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)

Not applicable
Author

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