
1. Re: Rank Function
Venu Babu Kolasani Feb 4, 2016 12:51 PM (in response to Anne Dahlstrom)Do you have the sample workbook?
Thanks,
Venu

2. Re: Rank Function
Melissa Davenport Feb 4, 2016 1:01 PM (in response to Anne Dahlstrom)I have the same questions. Need to show top or bottom 5 by YOY, but when ranking a table calc, the rank is all out of sorts.

3. Re: Rank Function
Jonathan Drummey Feb 4, 2016 1:23 PM (in response to Melissa Davenport)Besides manual and alphanumeric sorts, we can only sort dimensions (from primary sources) based on regular aggregate measures from the primary source, so that excludes table calculations. To get a table calculation to sort we need to do one of the following:
a) Set up a table calculation that returns the desired alphanumeric sort value for each discrete header pill that needs sorted. In the case of a sort descending on Sales, that means the negative of sales.
b) Change the table calculation into a regular aggregate measure, for example by using LOD expressions.
c) Change the table calculation into a blended aggregate where a selfblend is used to help get the equivalent table calculation result and then the blended aggregate is set up to return the desired alphanumeric sort.
In the attached I set up a) table calc and b) regular aggregate. For a) I used a second table calculation that returns the WINDOW_MAX([YoY Sales TC]) for every State, then put that pill to the left of the State pill in the view:
For b) I used a Level of Detail Expression to get the latest year in the data returned as a recordlevel results. Then the Current Year Sales is IF YEAR([Date]) = [Latest Year] THEN [Sales] END and the Prior Year Sales is a variation on that. Finally the YoY Sales measure is ZN(SUM([Current Year Sales]))ZN(SUM([Prior Year Sales])) and that can be directly used as a measure and sort the State dimension:
Jonathan

sorting YoY.twbx 86.3 KB


4. Re: Rank Function
Melissa Davenport Feb 4, 2016 2:11 PM (in response to Jonathan Drummey)Thank you Jonathan, option B worked great.

5. Re: Rank Function
Jonathan Drummey Feb 4, 2016 7:46 PM (in response to Melissa Davenport)You're welcome!
On Thu, Feb 4, 2016 at 5:12 PM, Melissa Davenport <