How to rank prior period value in chart with top 10 current period

[Anonymous]

New to QV, but after searching the forum and talking to others, unable to write a ranking expression as displayed in the fourth column below. For example Category 'a' is #1 in highest dollars current month, but last month it was just $5 and should last month be ranked category a to z (DESIRED RANK Last Month) it is ranked 11, but my rank expression (PRESENT RANK Last Month) just ranks it within the subset shown as 10. The chart has the dimension limited to top 10 of first expression (Current Month). I'd appreciate your thoughts.

Thank you

Category	$$ Savings:Current Month	$$ Savings: Last Month	DESIRED Rank Last Month	PRESENT RANK Last Month
a	100	5	11	10
b	90	90	2	2
c	80	80	3	3
d	70	70	4	4
e	60	60	5	5
f	50	50	6	6
g	40	40	7	7
h	30	30	8	8
i	20	20	9	9
j	10	100	1	1

[bbi_mba_76 · Accepted solution]

Hi,

I think you have to use the aggr function, see the attached example.

[Anonymous]

Awesome, thank you!

[tresesco]

BBI_MBA_76, Good one, however TOTAL keyword might not be essential here.