No in-depth tutorial available as of yet on this subject but it is potentially a good topic. I will consider this.
In the meantime, I did two things to your model:
1. I broke the RANK item out of the Quarters group. The sum(summary(Quarters)) formula was conflicting with the RANK formula causing a circular reference.
2. In the RANK function, you were calling an intersection of a category and an item in the same Quarter dimension. What you really want to do is slice across the Geography dimension and the Average of Quarters item… that is you want to rank the values of Average of Quarters across all the Geography items (states). This results in the RANK formula looking like this:
RANK = rank(Geography:Average of Quarters,Geography:Average of Quarters)
I have made these changes in the attached model. Hope this helps!
Ok, I am trying to do ranking in a difference format, and having some trouble here. In following the formula from another example, I know I am doing something wrong. My feeling is that I need to gain a better understanding of how to specify number and ranges within a particular category. Any in-depth tutorials on this?
Here is the model.
Thanks for all your help. I have just convinced my department to get Quantrix, so we will be ordering 3 commercial licenses. Of course, we still need to get through the process with our slow IT department, but my manager has approved. Yeah! I am so happy this happened before my 30 days ran out.
Kind of an odd one. Formulas look OK like you said. However, if I unprotect the sheet in Excel (Tools > Protection > Unprotect Sheet), edit the formula and press ENTER… the #VALUE goes away.
Not sure why it is happening – but looks like you can get the Excel sheet back in line by tickling the formulas. I have logged in our issue database for investigation.
Thanks for your reply! I tried to use your changes on the OFHEO matrix, but got some errors. When I used the rank formula, I got this error message ‘There is no element named “Item” …. ‘. Also, when the average formula does not have the summation sign in the ‘Average of Dates’ box. Can you please take a look? Tks.
Hey Carl – Thanks for posting:
I made a couple of modifications to the ‘NAR House Pricing Data’ matrix.
1 – I grouped the date items into a group called ‘Months’.
2 – I added an item called ‘Average of Date’ and created this formula:
Average of Date = average(summary(Months))
3 – I created a RANK item, and added the following formula to rank the date averages:
RANK = rank(Item:Average of Date,Item:Average of Date)
4 – I then created a view, swapped the category axis, and sorted the RANK item in ASC order (Tools > Sort Data > Sort Ascending).
And that did the trick! Please review the attached model file and feel free to post again if any questions.