I am trying to sort by descending.
It seems yours is working. How did you do that?
Actually I want to show top 5 for each Changed Reason. That's why rank is there.
As you scroll down, each Changed reason starts with a new High number.
I am using version 9.2 or 9.3. The newer version 10.1 is not supported by our server yet. Can you please help me figure out why it doesn't work with my version 9.2 or 9.3?
Im using 10.3 and am trying to figure how to sort and then rank the top 5 in each Change Reason. If I can get it figured out, I’ll let you know.
As soon as you're using INDEX() as a ranking calculation,
the Sort order option in the Edit Table Calculation dialog
is of the most importance. It should be set according to
the Measure of your choice, and in Descending order.
Besides, there are [Assignment ID] values in your dataset
where multiple rows with different [Changed Reason] exist
for the same [Assignment ID].
Thus the INDEX() Table Calc should be set to:
Compute using --> Specific Dimensions -->
both [Changed Reason] and [Assignment ID],
Restarting every --> [Changed Reason].
Another (common sense) question is regarding
the use of COUNT([Change Date]) aggregation --
instead of COUNTD([Change Date]) -- as a sorting Measure.
If every row with a non-empty Change Date should be counted,
then COUNT() is the right measure for this. On the contrary,
when only unique values of [Change Date] are taken into account,
then COUNTD() is the answer.
Last but not least, ranking different Assignment IDs --
each with the same value of the sorting Measure --
could be done differently (using DENSE, UNIQUE
or COMPETITIVE RANK with an upper or lower bound).
Each variant of RANK function gives a different result.
Please find the attached version 9.2 workbook
with two additional views explaining my points above.
Hope it could help understanding.
test_v92_YF.twbx 353.3 KB
Good work! I got exactly what I need!
Thank you so much for your help!
Glad Yuri got it sorted for you! Sorry I couldn't help