2 Replies Latest reply on Nov 20, 2012 6:43 AM by Claudia Barthel

    Calculated Measure with Condition

    Claudia Barthel

      I tried two different visualizations to show the number of visits of new and existing customers on a monthly basis. The type of customer is differs:

      • customer new > = 6/1/2012
      • customer existing < 6/1/2012
      • customer without startdate

      Why does Tableau add the 308 visits for customer without a startdate to 2 (a) new and 2 (b) existing?

       

      1. I created two Calculated Measures for Rows:

      visits_customer_new

      if ([customer_startdate]) >= date ("6/1/2012") then [visits] end

       

      visits_customer_existing

      if ([customer_startdate]) < DATE("6/1/2012") then [visits] end


      visits_customer_null

      IF ISNULL([customer_startdate]) THEN [visits] end


      2. I created one Calculated Dimension with Measure visit in Rows:

      (a) First the formula was:

      customer_type

      if ([customer_startdate]) < DATE("6/1/2012") then 'existing'

      else 'new' end

       

      (b) Now the formula is:

      if ([customer_startdate]) >= DATE("6/1/2012") then 'new'

      else 'existing' end


      For month Sep I get (somehow 308 are missing or added either to new or existing):

      1. number of visits
      2.(a) number of visits
      2.(b) number of visits
      visits_customer_new = 283new = 591new = 283
      visits_customer_existing = 1870existing = 1870existing =2178
      visits_customer_null = 308--
      Total visits = 2461Total visits = 2461Total visits = 2461
        • 1. Re: Calculated Measure with Condition
          Mark Holtz

          Hi Claudia,

           

          The difference, as you pointed out stems from NULL date values when you create your IF statements. In each formula, you have only 1 decision rule:
          in (a), you set anything WITH a date value < 6/1/12 to be 'existing', and all else as 'new'
          in (b), you set anything WITH a date value >= 6/1/12 to be 'new' and all else as 'existing'
          In both formulas, NULL values cannot be evaluated against the IF criteria, so the NULL values get assigned the "ELSE" value

           

          If you wanted to force (a) or (b) to result in the same thing, you would need to add a 2nd decision rule to each:
          IF [customer_startdate] < #6/1/2012# THEN 'existing'
          ELSEIF [customer_startdate] >=  #6/1/2012# THEN 'new'
          ELSE 'null/unknown'
          END

           

          Is this causing you a specific problem, or were you just curious? If you need more assistance, feel free to post a screenshot, packaged workbook, or just another specific question.

           

          Cheers

          • 2. Re: Calculated Measure with Condition
            Claudia Barthel

            Thanks a lot, Mark! That was definitely helpful.