Thursday, April 4, 2013

Using IF Statements in Tableau

Today, we will look at how to properly use IF Statements in Tableau, as well as a trick I find indispensable in most of my work.  Basically, an IF statement is a statement that returns different values based on a set of conditions.  A very simple example is as follows:

IF [Year] = 2012 THEN "2012" ELSE "Other" END

This statement returns the string "2012" if [Year] = 2012, and "Other" if [Year] <> 2012.  (For those who are less techy, <> is the "not equal to" operator.)  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:
  • Create a graph of Sales by Container
Sales by Container
Step 2:
  • Categorize the Containers by how much Sales they generate
  • Create the following calculated field
Container Category
Step 3:
  • Add [Container Category] to the graph to check the results
Sales by Container (with Container Category)
We've created a simple calculation that tells us how our Containers are performing, without having to scan through a list of numbers.  We could make this even easier by adding colors, or using something other than a text table.  However, we'll leave that up to you.

Let's shift over to that trick we mentioned earlier.  Let's say you want to calculate the total sales for your containers like we did earlier.  However, the Boxes should only be calculated for the last 30 days and everything else should be calculated for the last 45 days.  We've often received the question "How do you calculate a total over the last X days for one product and Y days for another?"  You're about to see!!!

Step 1:
  • Create the graph of Sales by Category
  • Filter the graph to only show the last 30 days
Wait a minute!!! If we filter the graph to only show the last 30 days, all of the containers will show the last 30 days; the same goes for 45 days.  How do we make them different?

Step 2:
  • Remove the Date filter (if you added it at all)
  • Calculate the 30 day sum for boxes and 45 day sum for the other categories
  • Create the following calculated fields
This is a complex step; so, we'll walk through it step-by-step.  First, decide which type of container you have as follows:
Container is Box?
Next, we need to create a parameter so that we can alter our anchor date for this calculation.  In practice, you could also use TODAY() or a custom field you create in the back-end.
Anchor Date
Now, let's use [Anchor Date] and [Container is Box?] to create our calculation.
Augmented Sales by Container
This graph is a little messy because of sparcity in the data.  However, you will notice that there are no [Sales] for Boxes before December 1, 2012, which is exactly 30 days before [Anchor Date].  We could check to make sure that the other Containers stop after 45 days, but it's easier to take my word for it and move on.  Finally, we can plot our new sums using whatever graph fits our fancy.
Final Graph
What makes this trick so neat is that the rows for days beyond 45 are still in the data set, yet they have no values for [Augmented Sales by Container].  This means that we could still add other types of data to this chart. (Egregious use of different time frames is not wise though.)  Also, the other aggregation functions, such as Average and Maximum, will still work on this data as well because Tableau does not consider NULL values when it calculates an aggregation.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn
Associate Consultant
Mariner, LLC
llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

13 comments:

  1. Hey Brad. This is helpful stuff - thanks! I am new to Tableau so forgive the potentially 'newbie' question but I am looking for a way to set the size of custom shapes dynamically and 'absolutely' if you will. In other words I don't want to all the sizes to be relative to each other (which seems to be the default in Tableau).

    To give you some context, I am building a dashboard that uses a background image of a retail store layout and I am placing a set of custom shapes on top - one for each dept. Each shape color will indicate sales for that dept. My issue is I want the custom shape to 'fill' the dept area below so each one will need to be a different size. Make sense?

    Any suggestions on the best way to do this? A CASE seems too manual but I guess could work if you can return a size attribute from the spreadsheet (which it doesn't look like you can). What is need is something like IF [deptName] = "sporting goods" THEN [deptSize] = "300". It also doesn't look like you can use arrays in Tableau which seems like it would help in this situation.

    Any thoughts appreciated!

    Thanks in advance!

    Greg

    ReplyDelete
    Replies
    1. Greg,

      Thanks for commenting! It sounds like what you really want is a polygon map. Check out this information and see if it helps.

      http://kb.tableausoftware.com/articles/knowledgebase/polygon-shaded-maps

      Delete
  2. Appreciate the response Brad. I did look at this option before the 'custom shape' option but it seems Tableau only accepts lat/long coordinates for shapefiles. YOu can't just feed in a bunch of x,y coordinates - which would be ideal. I will let you know what I figure out!

    Thannks again for your help!

    Greg

    ReplyDelete
    Replies
    1. Greg,

      Check out this video

      http://www.tableausoftware.com/learn/tutorials/on-demand/background-images

      Delete
  3. Hi Brad, I am tying to count PAYMENT <>0 as 1. here is my formula, if ([Pos Patient Payment])<>0 then count 1 else 0 END, but it gave me an error message saying expect end to match if at character 0. can you help advise? thank u!

    ReplyDelete
  4. Thanks for commenting! Between your THEN and ELSE you have a piece

    count 1

    This does not follow Tableau syntax and is likely the cause of the issue. Changing your code to

    IF [Pos Patient Payment] <> 0 THEN 1 ELSE 0 END

    should fix it. Does that help?

    ReplyDelete
  5. Hi,
    IF [deptName] = "sporting goods" THEN [deptSize] = "300".
    How do we implement this?
    Regards

    ReplyDelete
  6. Hi Brad.

    I want to use if condition for percentage of difference column. How do I create a caluculated field for that ?

    eg:

    2012 - 6.7%
    2013 13.2%

    I got the % by doing quick table calculation - percentage difference
    if it is greater than 0 then green else red

    Thanks

    ReplyDelete
  7. Dropping by to say thanks for this. I've read your blog many times and gotten good information. I've used IF statements before but today I was stuck and a short time here got me moving along quickly.

    ReplyDelete
  8. Hey Brad,
    I have a similar formula to build upon like,
    count [A] = count [B] yes
    count [A] = count [C] yes
    count [A] = count [D] yes
    else No
    end
    is this the correct approach? the formula is valid however I am not getting the correct output on the screen,
    kindly advice.

    ReplyDelete
  9. Brad,
    Can you see what is wrong with my IF?

    IF [WEEK] = ( max([WEEK])-1 )
    THEN 'LastWeek'
    ELSE 'Not LastWeek'
    END

    ReplyDelete
  10. I need help with this IF statement. I am using a database table and an Excel spreadsheet as my source data. Trying to add a calculated field using the following IF statement but keep getting error "Expecting THEN to match IF at character 25".
    IF [Var Cd] = 'OU'
    THEN IF [Var Amt]+ [Var Adj Amt1] < [T_DATA (FB)].[Over Under Min]
    THEN [T_DATA (FB)].[Over Under Min]
    ELSEIF [Var Amt]+ [Var Adj Amt1] > [T_DATA (FB)].[Over Under Max])
    THEN [T_DATA (FB)].[Over Under Max]
    ELSE [Var Amt]+ [Var Adj Amt1]END END

    ReplyDelete
  11. Hi Team,
    i need this calc should be clubbed for three measures,profit,sales,discount

    lookup(SUM([Sales]),0)>lookup(sum([Sales]),1)

    ReplyDelete