This tip was suggested by a friend of mine Will Perkins. Thanks Will!
Have you ever written an IF ELSEIF statement for criteria between different numbers? There is a simple way to write this statement that most users may not be aware of.
For example we want to add a tag for Sales with the following criteria;
For any Sales over 10K we want to put "$10,000+".
For Sales between 7500 - 10000 we want to put "$7,500 - $10,000."
Sales Between 5000 - 7500 enter "$5,000 - $7,500"
Sales Between 2500 - 5000 enter "$2,500 - $5,000"
Anything less than 2500 enter "<$2,500"
Some of you may have entered this criteria as the following formula format;
IF [Sales] > 10000 then "$10,000+"
ELSEIF ([Sales] > 7500 and [Sales] <= 10000) then "7,500 - $10,000"
ELSEIF ([Sales] > 5000 and [Sales] <= 7500) then "5,000 - $7,500"
ELSEIF ([Sales] > 2500 and [Sales] <= 5000) then "2,500 - $5,000"
ELSE "<$2,500"
There is an easier way to writing this formula out. It is also dependent upon the order you write your ELSEIF statements. You want to start out with the highest amount and then go down to the lowest amount.
Here is the example;
IF [Sales] > 10000 then "$10,000+"
ELSEIF [Sales] > 7500 then "7,500 - $10,000"
ELSEIF [Sales] > 5000 then "5,000 - $7,500"
ELSEIF [Sales] > 2500 then "2,500 - $5,000"
ELSE "<$2,500"
You don't need to specify the AND statements specifying the range that falls between the different amounts. It also takes up less time to write these statements!
This tip also applies to other applications and not just Alteryx. You can use this example for writing SQL statements and writing formulas in Tableau.
Comments