Today we learned about calling functions under a variety of categories, namely string functions, logical functions, numerical functions and datetime functions. In this blog I will walk through some common numerical functions that may prove useful as one explores data in any piece of software, be it Tableau, Power BI, Alteryx, or SQL (syntax notwithstanding).
Absolute Value (ABS)
The ABS returns the absolute value of a number, so if it would return a negative number, it flips this onto the positive axis. This function can be useful when one is dealing with values for which it doesn't make sense to have negative values, for things such as distance or time.
Sign (SIGN)
Sign considers the positivity or negativity of a number. If a number is positive it returns 1 and if negative it returns -1. This function has various uses and can be useful for colour-coding, for example, if one wanted to highlight any negative values on a chart, like profit per month percent change of some measure month on month.
Rounding Functions (ROUND, FLOOR, CEILING)
Various rounding functions can help round numbers so that they can be presented at an appropriate level of granularity. The three primary rounding functions are ROUND, FLOOR and CEILING. Let us consider the number 12.345. FLOOR rounds a number down to the nearest integer value, so 12.345 rounds to 12. CEILING rounds it up to the nearest integer, rounding our number instead to 13. The ROUND function rounds conventionally, and allows you to specify to what place value it rounds to. For example, ROUND(12.345,2) rounds up to 12.35 (i.e. to two decimal places). Oftentimes extraneous decimal values can be ignored for ease of use when extracting salient insights or stories.
Maximum and Minimum
MAX and MIN functions respectively return the larger and smaller of two values. Given two arguments, MAX(argument_1,argument_2) returns the larger of two arguments, and MIN(argument_1,argument_2) returns the smaller of two numbers. One can include more arguments, but MAX and MIN only work on pairs at a time, so one can use them recursively for a larger array of arguments.
So, for example, for three arguments, let's say you have Jan_Sales, Feb_Sales, Mar_Sales, and you wanted to return the value of sales for the best-selling month. MAX(MAX(Jan_Sales,Feb_Sales),Mar_Sales) will first return the larger number from January and February, and then return the larger of the two between that and March.
Zero Null (ZN)
Finally, ZN returns a zero for a value if no value exists. This can be critical when calculating averages as there may be cases when you want to include null values and others you you would like to exclude. If one took the average of three values, let's say 4, null, 6, the averaging functions in Tableau will ignore the null value, giving an output of 5. However, the ZN function will treat the null as 0, and average 4, 0, 6, giving an average of 3.333.