3 Replies Latest reply on Nov 30, 2016 10:10 AM by Samantha Absher

# Error in "if" "else if" calculated field

I am super new to Tableau and am using to do a rather large analysis. I need to get a cost per lead for various campaigns. So, I need Sum of Cost/Sum of Leads but the column that the lead is pulled from will vary depending upon the campaign. Here is a simplified example of my data:

 Date Assigned Campaign Campaign A Leads Campaign B Leads Campaign C Leads Cost 1/1/2016 Campaign A 1 1 3 1/2/2016 Campaign B 3 5 5 1/3/2016 Campaign C 5 3 2 1/4/2016 Campaign A 3 2 8 1/5/2016 Campaign B 7 6 2 1/6/2016 Campaign C 2 7 5 1/7/2016 Campaign A 7 4 7 1/8/2016 Campaign B 2 2 1 1/9/2016 Campaign C 9 5 7

Based on my limited experience, I thought the "if" and "elseif" function would work:

IF CONTAINS([Assigned Campaign], "Campaign A") THEN SUM([Cost])/SUM([Campaign A Leads])

ELSEIF CONTAINS([Assigned Campaign], "Campaign B") THEN SUM([Cost])/SUM([Campaign B Leads])

ELSEIF CONTAINS([Assigned Campaign], "Campaign C") THEN SUM([Cost])/SUM([Campaign C Leads])

ELSE

END

I keep getting one of two errors:

Syntax Error (maybe you are missing an identifier)
Cannot mix aggregate and non-aggregate comparisons or results in "IF" expressions.

Like I mentioned, I'm super new at this and I would really appreciate some help! Thanks!

• ###### 1. Re: Error in "if" "else if" calculated field

Hi Sam,

1. Look at the syntax for CONTAINS

2. You need to use ATTR to be able to fix the aggregate vs non-aggregate error.

IF ATTR(CONTAINS('Campaign A',[Assigned Campaign])) THEN SUM([Cost])/SUM([Campaign A Leads])

ELSEIF ATTR(CONTAINS('Campaign B',[Assigned Campaign])) THEN SUM([Cost])/SUM([Campaign B Leads])

ELSEIF ATTR(CONTAINS('Campaign C',[Assigned Campaign])) THEN SUM([Cost])/SUM([Campaign C Leads])

ELSE

END

Happy calculations!

Al

• ###### 2. Re: Error in "if" "else if" calculated field

Actually,

IF ATTR(CONTAINS([Assigned Campaign],"Campaign A")) THEN SUM([Cost])/SUM([Campaign A Leads])

ELSEIF ATTR(CONTAINS([Assigned Campaign],"Campaign B")) THEN SUM([Cost])/SUM([Campaign B Leads])

ELSEIF ATTR(CONTAINS([Assigned Campaign],"Campaign C")) THEN SUM([Cost])/SUM([Campaign C Leads])

END

1 of 1 people found this helpful
• ###### 3. Re: Error in "if" "else if" calculated field

Thank you!!! Worked perfectly