Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Aggr / Rank / top?

I have a chart  (pivot table) which shows the detail information of a transaction (the trx no, sale amount, product category, product group, product name ...)

I want to add a new field to calculate the ranking of the total sale amount grouped by category and product group.

Search the forum and found many experts mentions the use of aggr and rank but have no ideas on it.

Any advice is welcomed.

4 Replies
Highlighted

Perhaps this?

Rank(Sum(Sales)) //This is enough for you, I believe

Or

Sum(Aggr(Rank(Sum(Sales)), Category, [Product Group])) //This is only for cells of rows including Sub Total

Or

Aggr(Rank(Sum(Sales)), Category, [Product Group]) //This is only for cells of rows

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Thanks a lot.

Would you pls help to elaborate a little bit more of the 2 and 3 options?

For some case, the option 2 will return some numbers e.g. 1.5 (which I think it's odd for ranking).  

RankingSum(Aggr(Rank(Sum(Sales)), Category, [Product Group]))Aggr(Rank(Sum(Sales)), Category, [Product Group]) 
1-21.51-2
1-21.51-2

 

*******
And as if i create a new expression like below:
aggr(Sum(Sales), Category, [Product Group]), the calculated total only present in a few rows.  What's the logic?

Found the keyword Nodistinct  should be used in this case, just record here for other reference. 
*******

Highlighted


@yipchunyu wrote:

Thanks a lot.

Would you pls help to elaborate a little bit more of the 2 and 3 options?

For some case, the option 2 will return some numbers e.g. 1.5 (which I think it's odd for ranking).  

RankingSum(Aggr(Rank(Sum(Sales)), Category, [Product Group]))Aggr(Rank(Sum(Sales)), Category, [Product Group]) 
1-21.51-2
1-21.51-2

If two rows have the same Sum(Sales), the Rank divides the ranking on the two rows. There is a parameter within the Rank() function which you can use to get different values of rank when you run into those situations. I will look here to understand Rank() function here

Rank Function


@yipchunyu wrote:


And as if i create a new expression like below:
aggr(Sum(Sales), Category, [Product Group]), the calculated total only present in a few rows.  What's the logic?

Found the keyword Nodistinct  should be used in this case, just record here for other reference. 

If you more than 2 dimension in your chart and you are using the above expression, you can run into an issue which is called grain mismatch. You already found a work around (NODISTINCT), but read more on this here

Pitfalls of the Aggr function

Highlighted
Creator
Creator

Thanks a lot for all the advice and knowledge sharing.

I tried to apply the rank in other cases but got issues again (so sorry).

COMPANY_NAMEQUARTERTYPEYTQ_USD
A2018Q2Total9,832
B2018Q2Total1,158,893
C2018Q2Total0
D2018Q2Total0
E2018Q2Total0
F2018Q2Total198,191
G2018Q2Total98,876

 

I will select the Quarter and  type (including, total, channel1, channel2 ...) and then the column YTQ_USD will calculate the amount for that company by quarter by type.

I simply want to add a ranking by (YTQ_USD) by it doesn't work. (which either 1 or 0 being shown).

Anything goes wrong?

 

##update

Just trial and error and found below expression seems to work but can't fully understand the logic.

 Aggr(NODISTINCT Rank(Sum({$} #YTQ_USD)), COMPANY_NAME)