Forum Discussion
RankX in matrix with multiple hierarchy, multiple dimensions, and blanks
Super User
identical to the column total in each year
which of the column totals? Or do you mean rows (subgroup) ? How would you rank blanks versus negative numbers? Should you rank blanks?
The column total shown in the picture. So the rankings should be equal to the rankings on the far right of the table. Identical ranking across all years. Let me know if this makes sense. This ranking should be based on the absolute value of the sum across all years. An example:
Group 5, subgroup 1 should be ranked so that Type1 has rank 1 in 2020, 2021 and in the total, and Type2 has rank 2 in 2020, 2021 and in the total. This is because the sum of Type1 values is -32+0=-32, and the sum of Type2 values is -14+(-16)=-30. Since ABS(-32)>ABS(-30), Type1 will be ranked highest. This already works as it can be seen in the image in my post.
Blanks should be ranked, and treated as zero when calculating the above sum used for the ranking. My problem is, that, every place a blank appears in the data, the ranking for that cell simply outputs 1.
I hope this cleared up any questions. Thank you 🙂
- lbendlin
Blanks should be ranked, and treated as zeroI don't think I can agree with that, especially since you have negative values too. I hope someone else can help you further.