2 Replies Latest reply on Mar 5, 2014 8:52 AM by Cote Adams

    Using IF ISNULL Statement with Blended Data

    Cote Adams

      I'm in a catch 22. I'm trying to add a calculated column with a simple IF statement on a blended data set. There are 2 data sets (for this example, there is 1 column, and they are the same field). The CY dataset contains around 1100 records. The PY Dataset contains a subset of those records (700 records which ALL exist in the CY dataset). The purpose of this example is to demonstrate how easy Tableau is to use on a simple business case to an end user using data blending. They want the ability to see what all profiles are NEW to the CY dataset that don't exist in the PY using data blending (without pre-combining the data together or using SQL connection strings).


      After editing the relationships to join on the key field, I then created a calculated dimension on the PY dataset using the following logic:


      IF ISNULL([Profile Number PY]) = TRUE THEN 'New Profile'


      ISNULL([Profile Number PY]) = FALSE THEN 'Old Profile'



      The problem with this is it doesn't rename the ISNULL values to "New Profiles". They stay as NULL. The opposite records (not null) do show up as "Old Profiles" I can still color code and filter which is what the demo is trying to achieve, but would like to be able to display the nulls as "New".


      As a workaround, I created a calculated Measure with the following formula, and it works:

      IIF(ISNULL(ATTR([Profile Number PY])),"New Profile","Old Profile")


      The only problem with this is you can't FILTER on a measure.


      Am I missing something with the Dimension?


      Attached is an example packaged workbook. Ultimate goal is to have 'New" or "Old" based on PY being null or not, and the ability to use that value in a filter and mark.