I have a straight table with 2 dimensions (Category, Year). I am trying to take the sum of variable "Amt" by Category and Year and then show the maximum sum(Amt) by Year. I currently have an expression (Exp1) that has definition: Max(Aggr(Sum(Amt), Category, Year)). Basically, I need to see the priciest categories by year.
I also need to have a column that gives me the rank of Exp1, which is sorted in descending order. The highest value of Exp1 should have rank 1 and the lowest value of Exp1 should have a rank equal to the number of distinct values of dimension "Year."
When I define the Rank expression as Rank([Exp1]), Dimension "Year" expands to give me each Category for Year instead of showing each distinct year.
How do I define the expression properly that also will let me create a column that assigns a rank to Exp1 without affecting the displayed Exp1, Category, and Year?
