Dynamically created rank column

9.13K 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.



I have a related question. Besides the matrix and datasource I described above, I have another datasource that returns gross invoiced sales for all years and the posting date for invoices. Then in another matrix, where I utilize this data source, I categorize the sales data by year, using a DataNAV year qualifier.

Given the above, I now want to integrate this second matrix’s data into my first matrix as % of Sales. My question is:

How can two datasource (via DataNAV) based matrixes share the same time dimension? I tried using a third matrix date utility and I tried linking the year dimension from one to the other. Neither worked. I now imagine that the answer has something to do with using the meta functions @category() or similar. I can probably mess with it and figure it out, but any clues appreciated.

I need the year data from the first DataNAV-based matrix and the year data from the second DataNAV-based matrix to line up even when the user changes the year in the first matrix’s filter tray.


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