Dynamically created rank column
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!
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.
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.
Thanks!
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
