Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
lorevanachter
Partner
Partner

Nprinting PixelPerfect Ranking with set analysis and aggr()

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:

YearMonthrestaurantIdSales

% Sales performance
(Sales/other platform Sales)

(Rank) 
based on % Sales perf

201910

1

12320%2
201910

2

15624%1
201910

3

18818%3
201911

1

15024%1
201911

2

16023%2
201911

3

17020%3
201912

1

12018%3
201912

2

14020%2
201912

3

16023%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:

YearMonthrestaurantIdSales% SalesRank
201910112320%1
201911115024%1
201912112018%1

 

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

YearMonthrestaurantIdSales% SalesRank
201910112320%2
201911115024%1
201912112018%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.

Screenshot_1.png



Labels (2)
1 Solution

Accepted Solutions
lorevanachter
Partner
Partner
Author

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;

 

 


 

 

 

View solution in original post

3 Replies
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?

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
lorevanachter
Partner
Partner
Author

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

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;

 

 


 

 

 

View solution in original post