How to use a rank as column name in pivot tables

Question & Answer
1

How can i use a rank to name my columns in a pivot table? I´m using the Business Review dataset and i want to have in my columns: Channel, Sum of cost and the Rank of the channel, as Table 1

imagen
imagen1464×582 28.7 KB

I created this calculated fields:
concat(toString(extract(‘YYYY’, Date)),’-’,toString(extract(‘MM’, Date)))
sumOver : sumOver(Cost, [{Year - Month}, Channel],PRE_AGG)
rank sumover : rank([sumOver DESC], [{Year - Month}, Channel], PRE_AGG)
simple rank sumover : rank([sumOver DESC])

When I use my field rank sumover to name the columns all of them shows a 1 instead of 1,2,3
and it doesnt let me use my simple rank sumover to name my columns

imagen
imagen866×363 19.2 KB

2

Pro tip: do not give your calculated fields the same name as QuickSight’s internal function names because it makes things less readable :wink:

I suspect the issue is you are including Channel in the partition so each different Channel is within a different partition - and the ranking starts again from 1 within each partition… with your exisiting data this is resulting in all ranks to be 1.

So try changing the definitions like this:

sumOver : sumOver(Cost, [{Year - Month}],PRE_AGG)
rank sumover : rank([sumOver DESC], [{Year - Month}], PRE_AGG)

rank sumover should work this way and you should not need the simple rank sumover field.

3

Thanks for the reply, unfortunately with that calculations, all three channels get the um of the sales, so all have the same ammount of cost, and all are rank 1

imagen

And if use the channel only in the ‘sumOver’ calculation partition, i´m getting the cost correctly but the rank doesn´t show the correct numbers

sumOver : sumOver(Cost, [{Year - Month}, Channel],PRE_AGG)
rank sumover : rank([sumOver DESC], [{Year - Month}], PRE_AGG)

imagen

4

You are correct that you still need Channel for the sumOver partitions. However I think you need to use denseRank instead of rank, like so:

rank sumover : denseRank([sumOver DESC], [{Year - Month}], PRE_AGG)

The issue seems to be that lots of records are taking the same rank and so the next rank value will not be 2… denseRank does what you are expecting denseRank - Amazon QuickSight

1 Like
5

It does exactly what I needed, Thank you so much!