How to create date series with beast mode

wgechter

I'm trying to create a series of data: Same Day, 0-14 Days, 15-30 Days, 31-60 Days, etc

 

I have 2 fields Created_Date and Closed_Date.  If created_date = Closed_Date then "Same Day", if Closed_date - created_date < 14 days then 0-14 Days and so on.  

 

My beast mode looks like this below.  I was trying some stuff but anything over 30 days isn't working.  I've tried date_sub, datediff, subdate, day but nothing seems to be working.  

case when datediff(`Closed Date`, `Created Date`) = 0 then 'Same Day'
when DAY(`Closed Date`) - DAY(`Created Date`) < 14 then '0-14 Days'
when DAY(`Closed Date`) - DAY(`Created Date`) < 30 then '15-30 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 60 then '31-60 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 90 then '61-90 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 120 then '91-120 Days'
when DATEdiff(`Closed Date`, `Created Date`) > 120 then '> 120 Days' end

 

Thoughts?  Wendi

wgechter

Nevermind I just corrected it by doing datediff to all columns

 

case when datediff(`Closed Date`, `Created Date`) = 0 then 'Same Day'
when DATEdiff(`Closed Date`, `Created Date`) < 14 then '0-14 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 30 then '15-30 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 60 then '31-60 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 90 then '61-90 Days'
when DATEdiff(`Closed Date`, `Created Date`) < 120 then '91-120 Days'
when DATEdiff(`Closed Date`, `Created Date`) > 120 then '> 120 Days' end