It’s been awhile now that we’ve been doing LOD’s and complicated nested calculations. I wanted to go back and write about some simple calculations. Simple things get overlooked by people thinking that they already know them well but sometime on their work they notice that they aren’t fully aware of them. So without further ado, here are some basic calculations I wanted to go over.
LOOKUP()
Lookup essentially looks at the fields in view when computing the value. Above is an example with a LOOKUP function. Here I have my years in columns and the number of customers the company had in those years. By using the LOOKUP function, I can get the difference in the number of customers every next year, specified by the -1 at the end of the calculation. Here is the same calculation again:
SUM[Customer ID] – LOOKUP(SUM[Customer ID],-1)
FIRST() and LAST()
FIRST and LAST assign an exclusive number to each row of the dimension in the view. They can be used instead of INDEX or RANK if in need to assign numbers to our elements in view. The difference comes from how the numbers are assigned.
FIRST will put the first value as 0 and continue to count on a negative scale (-1,-2,-3…).
LAST will use the same principle but backwards. It will label the first value with the highest number and continue counting towards 0.
Another way to go about assigning numbers to our rows is with a RANK or INDEX functions. Rank will assign a number while ranking the dimensions which is useful for perspective. INDEX assigns the same format of numbers as rank (1,2,3,4…) but will not do it by looking at the measure. INDEX will assign the numbers without re-ordering the dimensions.
What if we have nulls we want to get rid of?
The 3 functions Tableau that deal with nulls in data (not counting the option to actually filter out the nulls) are: ZN, ISNULL and IFNULL Functions
ZN works only for measures and if within the specified measure we have a null, it will return a 0
IFNULL will look at a dimension and if there is a null it can replace it with whatever string we like. Example:
IFNULL ( Name, “NA” ) Here we have specified that we want a NA returned for nulls in our “name” dimension.
ISNULL also looks at dimensions but does not replace nulls but returns a Boolean True/False result. If we have a null it will return a True and if we bring Number of Records in we can get back how many nulls we have.