11 Replies Latest reply on Sep 20, 2018 6:10 AM by Soham Wadekar

# How do I correct my calculated field syntax?

I wish to create a calculated field, based on value in Visit Outcome and Offer Date fields. I have figured out the correct syntax in Numbers (Apple equivalent to Excel), but am unable to successfully apply the formula in my twbx. I am running Tableau 2018.2

1.) The syntax that works is in attached Apple Numbers iCloud doc (link attached): https://www.icloud.com/numbers/0jJoIyqz-_Gpdn7vMWtMvM5Mg#New-Existing_formulahttps://www.icloud.com/numbers/0jJoIyqz-_Gpdn7vMWtMvM5Mg#New-Existing_formula

In plain English, when a Visit Outcome="Accepted" and the Offer date is within 18 months of today's date, my new-existing calculated field value is "existing", if it is prior to 18 months, the value is "new". If the Visit Outcome is not equal to "Accepted", the new-existing field value is "na".

Grateful for assist.

Suzi

• ###### 1. Re: How do I correct my calculated field syntax?

Hi Susan,

Try something like this...

IF ATTR([Visit Outcome])="Accepted"

AND IF DateDiff('month',[Offer Date],Today())>18

THEN "New"

ELSEIF ATTR([Visit Outcome])="Accepted"

AND IF DateDiff('month',[Offer Date],Today())<18

THEN "Existing"

ELSEIF ATTR([Visit Outcome])!="Accepted" THEN "NA"

END

Regards,

Rahul

• ###### 2. Re: How do I correct my calculated field syntax?

Hi Susan,

You can do

IF  ATTR([Visit]) = "Accepted" and  ATTR( DATEDIFF('month',[Offer Date],TODAY()))<=18 THEN "Existing"

ELSEIF

ATTR([Visit]) ="Accepted" and   ATTR( DATEDIFF('month',[Offer Date],TODAY()))>=18 THEN "New"

ELSEIF  ATTR([Visit])!="Accepted" THEN "Na"

END

I could not find Accept in your "Visit Outcome" field so have used "Visit" - so please check that!

Let me know if this helps

• ###### 3. Re: How do I correct my calculated field syntax?

Hi,

I would do little different just to get 18 months from today's date. Since each year has 365 so 18 months has 547.5 days.

Result of above calculated filed

Hope this helps.

• ###### 4. Re: How do I correct my calculated field syntax?

Thank you for pointing out that it is the Visit field, not the Visit Outcome that has "Accepted" value.

I did "copy-paste" this formula into Tableau calc field. Do I need to +/- and carriage returns? Not familiar with this type formula:

IF ATTR([Visit])="Accepted"

AND IF DateDiff('month',[Offer Date],Today())>18

THEN "New"

ELSEIF ATTR([Visit])="Accepted"

AND IF DateDiff('month',[Offer Date],Today())<18

THEN "Existing"

ELSEIF ATTR([Visit])!="Accepted" THEN "NA"

END

Any other thoughts on what to try...

• ###### 5. Re: How do I correct my calculated field syntax?

Not sure if I understand  "Do I need to +/- and carriage returns?"

But the formula assigns anything which is Accepted from today until the next 18 months  as Existing, 18 months older is New and anything other than "Accepted" will have NA. the Attr() is an Attribute function which checks if each record is unique -> if each row is unique it returns the value else it returns an * .

So if you see below - for Year= 2018 and visit = Accepted  the result is Existing

2.  for 2016-> Accepted -> Result is New

all others except for Accepted will be NA`s

If you try to put this field on the maps -> you will see some of the New-Existing  as blanks and that`s because they are aggregated on a territory level so they have more than 1 unique values and thus no result will be returned.

Please let me know if this makes it clear or more confusing

• ###### 6. Re: How do I correct my calculated field syntax?

Thank you for clarifications.

I see the formula works for you. Still not sure why my calculated field is still giving me a red !.

And, yes, I understand issue with displaying on map that is aggregated at territory level.

Re: carriage returns

I simply copy-pasted your formula into the calculated field box, and was trying to further isolate why I am unable to get the results in your screen shot.

Thx. Will try it again.

• ###### 7. Re: How do I correct my calculated field syntax?

Can you post a screen shot of your formula/ calculated field?

• ###### 8. Re: How do I correct my calculated field syntax?

Did you try the formula I shared in my earlier response??

IF [Outcome] = 'Accepted' AND DATEDIFF('day',[Offer Date],TODAY()) <= 547.5 THEN 'Existing'

ELSEIF [Outcome] = 'Accepted' AND DATEDIFF('day',[Offer Date],TODAY()) > 547.5 THEN 'New'

ELSE 'N/A' END

• ###### 9. Re: How do I correct my calculated field syntax?

Thanks again for you continued help.

I do prefer your formula using months instead of days. I will try this in a couple of hours when I return online. Thank you!

• ###### 10. Re: How do I correct my calculated field syntax?

Your calculation worked as originally proposed. The issue that was creating an error on my end: the field offer date was set as a date & time. When I changed it to date, it changed to "calculation is valid". Thank you again for your kindness, patience, lesson and Correct Answer!

• ###### 11. Re: How do I correct my calculated field syntax?

Great! I am glad it worked!