Dynamically created rank column

9.12K viewsTips and Techniques
0

Hi –

I have a matrix attached to a datasource. The matrix has three categories:

1. Customer #

2. Item

3. Year

Columns associated with the Item category are (from left to right):

1. Rank

2. Customer name

3. Total Sales

Customer #, Customer name, and Total Sales are supplied by the datasource.

The Year category is a DataNAV qualifier that allows columns associated with the my Item category to be in year buckets, 2008, 2009, 2010, etc..

The Year category tile is configured to be in the filter tray so as to filter by year.

The Total Sales column is sorted in descending order so that the highest sales appear at the top of the list for any given year.

Here is my question:

How would I get the Rank column to auto-populate itself (what would the formula be) based on the Total Sales order for the current year shown (and change appropriately as the year filter value is adjusted)?

Example to illustrate what I would like:

Customer #, Rank, Customer Name, Total Sales
C29128, 1, Customer 2, $500,000
C19283, 2, Customer 1, $335,000

I tried to find some way of using the Rank function, but to no avail.

Thanks!

0

Thanks Mike. I’ll have to keep them separate in this case, but I appreciate the tip about select and using as anyway and thanks for taking time to make the example model. +1 for “joining” datasources.

You are viewing 1 out of 6 answers, click here to view all answers.