Dynamically created rank column

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

Hi – thanks for the post:

The trick with Rank when using it with values in the same matrix is to call the customer category in the function. The reason is that forces Quantrix to build the list of values. Otherwise, it will only evaluate one value at a time.

So the formula would look something like this:

Rank = rank(
Total Sales:’Customer #’,
Total Sales:’Customer #’)

I have also attached a model file to demonstrate.

Hope this helps! Feel free to post again with any questions.

-Mike

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