Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jessiyeajin
New Contributor

Rank expression in straight table

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?

1 Reply
Frank_Hartmann
Honored Contributor II

Re: Rank expression in straight table

please share qvw or rawdata