Dates

[suby]

Hi All,

My Column 'date' is calculated using the Date diff formula by using two dates using months as the Unit

Then after that i am trying to create a Bucket Column using the below formula and its not working as i am getting an error.

IF date1 > 0 AND date1 <= 3 THEN '0-3 Months' ELSEif
date1 > 3 AND date1 <= 6 THEN '3-6 Months' ELSEif
date1 > 6 AND date1 <= 9 THEN '6-9 Months' ELSEIF
date1 > 9 AND date1 <= 12 THEN '9-12 Months' ELSEIF
date1 > 12 AND date1 <= 24 THEN 'MORE 1 YEARS' ++' ELSEIF
date1 > 24 THEN 'MORE 2 YEARS' ++' else isnull(date1) then 'N/A' endif

Secondly i want to implement the same above formula in 'date2' column where i can create another formula which is fine.

but what i am looking for is i need one month bucket formula to be applied in date1 and date2 column using multi field formula please.

[binuacs]

@suby you may need to rewrite your formula like below

IF date1 > 0 AND date1 <= 3 THEN '0-3 Months' ELSEif
date1 > 3 AND date1 <= 6 THEN '3-6 Months' ELSEif
date1 > 6 AND date1 <= 9 THEN '6-9 Months' ELSEIF
date1 > 9 AND date1 <= 12 THEN '9-12 Months' ELSEIF
date1 > 12 AND date1 <= 24 THEN 'MORE 1 YEARS'

ELSEIF date1 > 24 THEN 'MORE 2 YEARS’

Else ‘NA’

EndIf

[ChrisTX]

What is the error?

In the Formula tool, use double quotes and set the data type to be a string type, like String(10) or v_String(10).

Is the date1 data type a number?

IF date1 > 0 AND date1 <= 3 THEN "0-3 Months" ELSEif

You want to use a multi-field tool, but I don't understand the different formulas.  Are formula 1 and 2 the same?

You mentioned "i want to implement the same above" and "where i can create another formula". 

If the two formulas are exactly the same, you can just create a new second field in the Formula tool with date2 = [date1].

You mentioned you want a one month bucket formula.  Did you mean a one quarter bucket?

and the final ELSE segment cannot have an IF or THEN, and remove the ++

 ++' else isnull(date1) then 'N/A' endif

change to      Else "N/A" EndIf

Chris

[suby]

Hi Chris,

You're right and the issue is caused due to the Datatype which works now thanks.

date1

DateTimeDiff(DateTimeNow(),[date1],'months')

date 2

DateTimeDiff(DateTimeNow(),[date2],'months')

now i wanted to create one column called 'Bucket' which should look at date1 values and date2 values and create a months bucket say 0-3 months and 3-6 months etc using a multifield formula.

[suby]

Thanks Binuacs.

[binuacs]

@suby can you provide a sample file with expected output result ?  

[fharper]

I think the snippet style explanation left us a bit unclear but I think I get it.  you have 2 dates and you want to calculate the diff in months for each date1 and date2, then produce 2 bucket columns, one for each date diff value, date1 & date2. 

to use the multi-field Formula in this scenario so you write the formula once you can

1. in the formula tool where you already calculate date1 & date2, the differences number of months. create the datebucket1 & datebucket2 with null v string 50 char perhaps...
2. In the Multi-field Formula write the single formula referencing currentfield and selecting the 2 bucket columns 

about to hop a flight so I can't provide a flow example but the explanation should get you there.

you can probably integrate the data difference calculation into the formula and avoid creating the date1 & date2 columns if you would not need them after this point.

[suby]

Attached the sample data.

date1
DateTimeDiff(DateTimeNow(),[P_DATE1],'months')

date2
DateTimeDiff(DateTimeNow(),[P_DATE2],'months')

I need a way to take the values from date1 and date2 and group them and then create a one new column
called buckets with 0-3 months and 3-6 months etc...

but 

in Row number 3 on my sample data - date1 falls under 3-6 months and date2 falls under 0-3 months.

I need a way where when i filter the new buckets column  for 0-3 months row number 3 should be displayed and when i filter 3-6 months row number three should be displayed or is  this possible to achieve ?

Many Thanks

[fharper]

@suby 

I wrote this out once already but it disappeared...

I am providing an example of what i think you want.  your description wants you to be able to filter on a single field for two conditions that may exist simultaneously and that doesn't make sense to me.  so maybe there is something I missed in how you will filter, via app selection or what.  To do that you need to replicate rows for each unique key and not sure that is where you want to go.

Anyway I do not think a Multi-Field Tool works well for this due to 2 scenario derived from 2 sets of fields.  I explained more fully but not going to again in a second pass at posting this.

I created 2 more buckets, Bucket1 & Bucket2 and used Buckets as the repository for the lesser of Buckets 1 or 2 values in case that is where you were intending on the filtering.

I used "Find & Replace" tools to convert the difference integer value to a text range value.  I liked it better but if there were 10s of millions of rows i might do it in a Formula as it might be a little more efficient.

I think it works for you and if not it may be enough that you can run with it and get where you want to be.

In either case let us know your thoughts.

[fharper]

for some reason its not uploading the very small yxzp file