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

# Calculated Measure with Condition

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

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

Thanks a lot, Mark! That was definitely helpful.