Nested If when creating custom field error

[femi_owoseni]

I used this expression successfully when I created a Master Item - Dimension. When I tried to use the same expression to create a custom field I keep getting a Missing right parenthesis error.

=if((floor(today(1)) - floor(Date_Uploaded))/365 <1, '<1yr',

if((floor(today(1)) - floor(Date_Uploaded))/365 >=1 and (floor(today(1)) - floor(Date_Uploaded))/365 <2, '1-2 Yrs',

if((floor(today(1)) - floor(Date_Uploaded))/365 >=2 and (floor(today(1)) - floor(Date_Uploaded))/365 <3, '2-3 Yrs',

'3+ Yrs')))

[Lisa_P]

Syntax is ok for me.  I just copied and pasted your expression and changed the field names to match my data and it worked as a calculated dimension in a bar chart.

[femi_owoseni]

Thanks for confirming the syntax Lisa. The problem is, that same expression returns an error when used to create a custom field in data manager.

[Lisa_P]

I see what you mean.  Not sure about that issue in the data manager.

It is easy to do in the script.(Data Load Editor)

If you want to keep loading your data with data manager, you can just add another script section at the end of the script in the Data Load editor, Reload the table with the Date_Uploaded field and add in your statement like this:

NewTable:

Load *,

     if((floor(today(1)) - floor(Date_Uploaded))/365 <1, '<1yr',

          if((floor(today(1)) - floor(Date_Uploaded))/365 >=1 and (floor(today(1)) - floor(Date_Uploaded))/365 <2, '1-2 Yrs',

               if((floor(today(1)) - floor(Date_Uploaded))/365 >=2 and (floor(today(1)) - floor(Date_Uploaded))/365 <3, '2-3 Yrs',

                    '3+ Yrs'))) as YearsAgo

Resident OldTable;

Drop table OldTable;

This will create your calculated dimension for you in the script.

[femi_owoseni]

I wanted to do it in the data Manager so I save my users the trouble of going to the load editor. Trying to make it easy for users not comfortable with scripts and complex functions to use my data model.

I did however find out 2 things

1 - the data manager doesn't like the and operator but & works fine (that got rid of the missing right parenthesis error).

2 - My workaround was to create a custom field using (floor(today()) - floor(CI_Date_Uploaded))/365 as Age then use the Bucket functionality to group it and presto problem solved.

Thanks for your help.