Rank Based on the X-axis

• 

  Ole Dyring

  July 15, 2021 07:22

  Hi Danilo,

  As you have realized, the rank calculations unfortunately only work for ranking rows - not for ranking columns. This is a consequence of the current implementation of the crosstab object.

  So, when it comes to ranking columns in a crosstab, we will need other means. It is far more tedious than the rank calculation itself, and if it is a full ranking sequence that we need, I will not recommend it. But when it is for a limited number of top/bottom ranks - like 4 in your case - I think it may be worth dong it.

  My approach:

  If we are looking for the bottom rankings, I would at first look for the minimum value among all values. That will of course give us the lowest ranking member. To find the second-lowest value, I will have to copy all the original values, but somehow 'eliminate' the previous found minimum value - e.g. by making sure that it is now greater than the maximum value. I can now find a new minimum value (the second-lowest) value in the new data set. Etc.

  Example:

  

  In this example I will try to locate the bottom 4 months. By eyeballing, we can see that this is in fact July, May, June and January.

  if sum(0, d1(l0), m1) = min(all, d1(l0), m1) then 1 else 0

  I am using this formula to point out the minimum value among my Month Totals. Notice that sum(0, d1(l0), m1) is a reference to my Month Totals.

  

  Now, I will change this formula slightly to this:

  if sum(0, d1(l0), m1) = min(all, d1(l0), m1) then 10001 else sum(0, d1(l0), m1)

  To get a result like this:

  

  The value 10001 is just something that I am sure is not the lowest value among Month Values. You could also replace 10001 with max( all, d1(l0), m1) + 1 if you don't know a fixed value that will work in all cases.

  Next, we do the same thing on the recently added calculated row (referred to as c1) to find the new minimum value which happens to be our bottom 2 value, etc. To find the bottom 4 values, we will need 4 calculated rows, i.e. three more in addition to the one above:

  c2: if sum(0, c1, m1) = min(all, c1, m1) then 10002 else sum(0, c1, m1)
  c3: if sum(0, c2, m1) = min(all, c2, m1) then 10003 else sum(0, c2, m1)
  c4: if sum(0, c3, m1) = min(all, c3, m1) then 10004 else sum(0, c3, m1)

  This will produce a result like this:

  

  Now we just need a visibility agent to hide the Months (action: hide Month members) where values in our c4 calculation is less than 10000.

  

  Also, use general Visibility options to hide the four calculated rows. And, eventually, when we turn the result into a chart, it may look like this:

  

  BR / Ole

   

  0
• 

  Danilo Rufino

  July 15, 2021 13:58

  Thanks Ole, I will give this a try, but it seems like it would work.

  The real case scenario has way more columns and complications, but the logic for it should work the same.

  BR,

  Danilo

  0

Please sign in to leave a comment.