Archives: RANK_UNIQUE

LOD equivalent of INDEX

Is there a level of detail expression equivalent of a particular table calculation in Tableau? This question comes up on the Tableau community forums every now and then, and almost always intrigues me.

Often a level of detail (LOD) expression isn’t really necesary, but occaisionally an alternative to table calculations is necesary. And like a mountain to be climbed, or a trail to be explored, I’m fascinated by whether it is even possible to implement LOD equivalents to some common table calcs.

I’ve previously posted on why this is a challenge, and how we can implement a LOD equivalent of LOOKUP in certain situations.

But what about INDEX? Or RANK_UNIQUE? The answer is yes, but I’ve only achieved it in quite specific situations:

  • calculating an index or rank over whole numbers only
  • where the numbers are all unique
  • and cover a relatively small range (though you can get around this as I discuss at the end of the post)

I’m posting about this in case it helps others, or triggers further ideas to improve the approach.

Solution walkthrough

For this walkthrough I’m going to use the following test data:

We have a list of fruit, each of which has a value. And we want to rank the fruit by that value. With a table calc this is easy! The third column below uses INDEX() computed by table down for example:

Firstly the big constraint: with a LOD we can jump out of the level of detail we have, to a higher or lower level of detail. However there really is no level of detail that tells us how many records have come before or after a particular record, as that before/after “group” is different for each record and so isn’t actually a different level of detail. My thinking is that we can’t simply use a LOD that counts the values (COUNT or COUNTD) before a particular record.

However, we can use a LOD to SUM up values across the data. And if any given value always had a discernable position within that SUM we might be able to derive an index. At this point my mind turns to bits in binary numbers, or converting numbers to and from base 2.

In binary:

  • 1 is represented by 001
  • 2 is represented by 010
  • 3 is represented by 011
  • 4 is represented by 100
  • 5 is represented by 101, and so on

Perhaps we can switch on “bits” (the 1s above) at the position for each value we have and use that…

Unfortunately Tableau doesn’t offer great binary arithmetic but we can use strings and a brute force calc (this is where the limit on the number range comes in).

The LOD version in purple matches the table calc version in green

And here is breakdown of the results of each step of the calc…

LOD INDEX


Let’s take a look at how it works…

x1 =

[Value]-{FIXED:MIN([Value])}

The x1 calc ensures that the range of values starts at zero by subtracting off the minimum value across the data set (note that if you’re filtering your data you’ll need those filters to be context filters, or include them as a list of dimensions you’re fixing by between FIXED and the colon).

Next we convert x1 into a binary number, switching on the bit (from 0 to 1) at the right position.

x2 = 

POWER(2,[x1])

And we can sum that up over the whole data set using a FIXED LOD (noting again that you’ll need to take into acount any filters on your view, adding to context or the dimensions fixed by).

x3 = 

{FIXED:SUM([x2])}

This number (144243 in the screenshot above) isn’t that useful as a normal decimal number. We need to convert it back to binary, and we’ll need to be able to work with that binary string of 1s and 0s. Annoyingly we need to brute force this as you’ll below!

x4 =

// ref: https://help.salesforce.com/s/articleView?id=001456706&type=1
// should handle numbers up to ~1M
STR(INT(([x3]/2^20))%2)+
STR(INT(([x3]/2^19))%2)+
STR(INT(([x3]/2^18))%2)+
STR(INT(([x3]/2^17))%2)+
STR(INT(([x3]/2^16))%2)+
STR(INT(([x3]/2^15))%2)+
STR(INT(([x3]/2^14))%2)+
STR(INT(([x3]/2^13))%2)+
STR(INT(([x3]/2^12))%2)+
STR(INT(([x3]/2^11))%2)+
STR(INT(([x3]/2^10))%2)+
STR(INT(([x3]/2^9 ))%2)+
STR(INT(([x3]/2^8 ))%2)+
STR(INT(([x3]/2^7 ))%2)+
STR(INT(([x3]/2^6 ))%2)+
STR(INT(([x3]/2^5 ))%2)+
STR(INT(([x3]/2^4 ))%2)+
STR(INT(([x3]/2^3 ))%2)+
STR(INT(([x3]/2^2 ))%2)+
STR(INT(([x3]/2^1 ))%2)+
STR(INT(([x3]/2^0 ))%2)

You’ll need to adjust this to suit your range of numbers and will almost certainly hit limitations with anything bar small ranges of numbers (you can see that I needed 17 lines of the above calc just to cater for a range of 3 to 20!)

The binary string we end up with, 000100011001101110011, has ten 1s in it (corresponding to the 10 values we had). With the position of each 1 representing it’s value (adjusted so the range starts at 0).

From here we can say “take the digits to the right of my position” for any given number.

x5 = 

RIGHT([x4],[x1]+1)

And that does give us a chance to count the numbers up to and including this number … by counting the 1s. We can do this by removing the 0s and seeing how long the remaining string is…

i LOD = 

LEN(REPLACE([x5],'0',''))

Which gives us our LOD equivalent of INDEX!

Hang on, what about…

What if I need a larger range of numbers? You may be able to extend the large 2^X string function above to cater for more digits.

What if that isn’t helping, or I need to index strings not numbers? You could pre-calculate an index in the data set (using Tableau Prep say), and then use the technique above on the pre-calculated index to allow for cases where rows are filtered out in the view. Assumption here is that the pre-calculated index will span a smaller range than the original numbers (or ensure you have a number in the first place if working to index strings). This was the approach taken in the forum post I shared above, and the challenge was adusting the pre-calculated index as the view was filtered … without using a table calc.

What other alternatives are there to table calcs and LODs? Self joining your data can allow you to see how many records fall before any given record, and that can be a simpler solution. E.g. join the data to iself where A.value >= B.value … means every row in A links to any row with the same or lower value.

So why do this with LODs? You really shouldn’t have to do this very often, if at all! Editing the compute by for table calculations often solves cases where you don’t think you can use a table calc. In the case that triggered this the results needed to be used in MAKEPOINT which didn’t accept a table calc.

Is that the only reason? No, for me it’s also a fun challenge. And one I don’t feel like I’m fully done with … if you find it intriguing too, can you take the idea further? Or do you have better ways to calculate and INDEX or RANK with a LOD for each row in a data set/view?