Nprinting PixelPerfect Ranking with set analysis and aggr()

[lorevanachter]

Hi all,

Currently, I am trying to get a ranking calculation done in QS so I can use it for my Nprinting report.
The ranking checks the % Sales Performance for every month compared to the other restaurants.
When I try this with the rank() function, I am not achieving the desired result. I tried set analysis and aggr () but think that I am doing something wrong.

I was looking at the following post, which is of someone with more or less the same issue.

https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Nprinting-report-rank-with-filter/m-p/16597...

Please help me find the correct calculation.

What I have in QS with NO SELECTIONS:

YearMonth	restaurantId	Sales	% Sales performance (Sales/other platform Sales)	(Rank)  based on % Sales perf
201910	1	123	20%	2
201910	2	156	24%	1
201910	3	188	18%	3
201911	1	150	24%	1
201911	2	160	23%	2
201911	3	170	20%	3
201912	1	120	18%	3
201912	2	140	20%	2
201912	3	160	23%	1

This is the calculation for % Sales:
count(distinct saleId)/sum(aggr([Guest Counts],restaurantId,Date))

For rank:
rank(count(distinct saleId)/sum(aggr([Guest Counts],restaurantId,Date)))

When I select a restaurant:

YearMonth	restaurantId	Sales	% Sales	Rank
201910	1	123	20%	1
201911	1	150	24%	1
201912	1	120	18%	1

IDEALLY, I would get the following when selecting a restaurant:

YearMonth	restaurantId	Sales	% Sales	Rank
201910	1	123	20%	2
201911	1	150	24%	1
201912	1	120	18%	3

Where rank is still following the benchmarking(%Sales). (In a bigger dataset it might happen there is no rank 1 when selecting a restaurant)

In NPrinting, I have the same table that I need to build.
My report is in a cycle by restaurantId and in the template, the table is inside a YearMonth level.

Hopefully, someone can help me find the set analysis/aggr() that I need to use because I can't seem to find it.

Below a screenshot of the part of the model that is being used.

[Lech_Miszkiewicz]

Like I mentioned before I am not sure about your aggr function, but i did some quick test and this is what formula I would use:

If(count(distinct saleId)=0,0,rank(count(distinct {<restaurantId=>} saleId)/sum( {<restaurantId=>} aggr([Guest Counts],restaurantId,Date))))

the reason for Ïf statement"--> If(count(distinct saleId)=0,0.... is to hide rows which will allways produce data once you start cycling over restaurantid field.

btw - why do you need to use aggr fuction? Cant you just use sum([Guest Counts]) in second part of your expression?

[lorevanachter]

Thank you for your help @Lech_Miszkiewicz  🙂

The weird aggregation is because these numbers are provided on a different aggregation level than the other calculation (count distinct). Even though the calculate the same thing for different sources. 

Apart from that, my coworker eventually found the solution. I will post it in a different reply to have a clear solution post. 🙂

[lorevanachter · Accepted solution]

Replying to my own post as my coworker found the solution.

The ranking seemed very difficult, if not impossible, to try and solve in the frontend, so we decided to get back to the script and statically calculate the ranking on the aggregation level needed. This way we can be sure that the ranking is not variable to selections made in the dashboard.

Hereby the sample code used:

Table1:
load * inline
[YearMonth, restaurantId, restaurantName, Sales, %Sales
201910,1,A,123,0.2
201910,2,B,156,0.24
201910,3,C,188,0.18
201911,1,A,150,0.24
201911,2,B,160,0.23
201911,3,C,170,0.20
201912,1,A,120,0.18
201912,2,B,140,0.20
201912,3,C,160,0.23
];

temp:
NoConcatenate
load *,
	
	if(isnull(peek(YearMonth)),1,
    	if(previous(YearMonth)=YearMonth,peek(Ranking)+1,1)) as Ranking
    resident Table1
    order by YearMonth, %Sales desc;
    drop table Table1;