I have a crosstab similar to below (based on a Transformer Cube):
Score
Store1 WTD 99
MTD 105
QTD 102
YTD 102
Store2 WTD 97
MTD 106
QTD 101
YTD 104
I need to add a Ranking for each period based on the Score such that it looks like:
Score Chain Rank
Store1 WTD 99 1
MTD 105 2
QTD 102 1
YTD 102 1
Store2 WTD 97 2
MTD 106 1
QTD 101 2
YTD 100 2
Store is part of the All Stores, Region, District, Store Hierarchy (and I may also need rankings at Region/District as well as Chain).
I currently have the Rank calculation as rank ([Score] within set [All Stores])
The result is blank/null.
Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.