topic Ranking - Top 20 Next 20 in QlikView

Ranking - Top 20 Next 20

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 09:24:12 GMT

you can try expression something like

=if(rank(sum(Sales),0,1)>=21 and rank(sum(Sales),0,1)<= 40,sum(Sales))

Re: Ranking - Top 20 Next 20

devarasu07 — Mon, 18 Sep 2017 09:28:13 GMT

Hi,

Try like this,

=aggr(if(Rank(sum(Salary))>=21 and Rank(sum(Salary))<=40,Rank(sum(Salary))),Employee)

Re: Ranking - Top 20 Next 20

tresB — Mon, 18 Sep 2017 09:28:32 GMT

If you have used a rank() in expression for creating the top 20 chart, the same expression could be modified for the 21-40 ranked sales. Say you have used expression something like:

=Sum({<SalesMan={"=Rank(Sum(Sales))<=20"}>}Sales)

for 21-40, modifiy it to something like:

=Sum({<SalesMan={"=Rank(Sum(Sales))>20 and Rank(Sum(Sales))<=40"}>}Sales)

Re: Ranking - Top 20 Next 20

patroser — Mon, 18 Sep 2017 09:33:53 GMT

Regarding the performance I'd recommend using set analysis, you could try it with this expression:

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

Regards,

Patrick

Edit 19.09.17: as kush141087‌ mentioned, the intersection is not as performant as the "and" operator:

Therefore

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

should perform better

Re: Ranking - Top 20 Next 20

Anil_Babu_Samineni — Mon, 18 Sep 2017 09:35:04 GMT

As i know there is no function Rank() in script, But if really want to achieve then i would prefer like below

Fact:

LOAD * Inline [

Name, Sales

Rajendra, 10

Anil, 20

Weel, 30

];

Fact_Copy:

LOAD Name,

sum(Sales) as Total_Sales

Resident Fact

GROUP BY Name;

Fact_S_Copy:

LOAD Name,

RowNo() as Rank,

Total_Sales

RESIDENT Fact_Copy

ORDER BY Total_Sales asc;

DROP TABLE Fact_Copy;

Table:

LOAD Name,

Total_Sales,

if( Peek('Total_Sales') = Total_Sales, Peek('Dense_Rank'), Rank) as Dense_Rank

RESIDENT Fact_S_Copy

ORDER BY Total_Sales asc;

DROP TABLE Fact_S_Copy;

DROP Field Sales;

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 09:38:38 GMT

or better use set analysis

=sum({<SalesRep ={"=rank(sum(Sales),0,1)>=21 and rank(sum(Sales),0,1)<= 40"}>} Sales)

Re: Ranking - Top 20 Next 20

patroser — Mon, 18 Sep 2017 09:40:07 GMT

Isn't the problem in this case that you precalculate a rank which doesn't react to selections.
Therefore selecting a specific Product group wouldn't actually give the correct rank for this group but for the whole precalculation?

Re: Ranking - Top 20 Next 20

tresB — Mon, 18 Sep 2017 09:48:23 GMT

Hello Dave,

Have you checked and sure the expression provided by Patrick really works? You marked it correct, however, the expression looks not correct to me.

Re: Ranking - Top 20 Next 20

Anil_Babu_Samineni — Mon, 18 Sep 2017 09:51:42 GMT

I thought, I've replied for this..

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

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 09:52:44 GMT

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

Re: Ranking - Top 20 Next 20

patroser — Mon, 18 Sep 2017 09:55:20 GMT

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

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 09:56:20 GMT

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)

Re: Ranking - Top 20 Next 20

patroser — Mon, 18 Sep 2017 09:56:29 GMT

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!

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 09:58:36 GMT

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)

Re: Ranking - Top 20 Next 20

patroser — Mon, 18 Sep 2017 10:02:56 GMT

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

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 10:10:07 GMT

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?

Re: Ranking - Top 20 Next 20

Mon, 18 Sep 2017 11:52:40 GMT

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

Re: Ranking - Top 20 Next 20

Kushal_Chawda — Mon, 18 Sep 2017 12:01:11 GMT

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)

Re: Ranking - Top 20 Next 20

Mon, 18 Sep 2017 12:13:19 GMT

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