Dynamically created rank column

9.12K viewsTips and Techniques

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.



Hi – you can link dimensions between matrices that share the exact same data source. Sounds like you might have two different data sources in play which will make things a bit more difficult.

If that is the case, then some type of Using As or Select Statement is needed to bring over the yearly information from one matrix to the other. There will not be a way to tie the qualifiers for each matrix, so each will need to be changed individually.

I have attached a demonstration model showing how to use the Select against multiple categories.

“Joining” multiple datasources is something we are considering for future versions of DataNAV. I will add you vote to this request in our database.

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