Date bucket

[abc_18]

Hi All,

I have 'Created Date' field which is in format of  3/18/2013  10:38:11 AM. 

I need to create buckets for the dates like below

'0-5 Days' , '6-10 Days' ,'11-15 Days' and ">15 Days"  .

[Anil_Babu_Samineni · Accepted solution]

I would ask you to do this?

Load *, If(Day >=0 and Day <= 5, Dual('0-5 Days',1),  If(Day >=6 and Day <= 10, Dual('6-10 Days',2),  If(Day >=11 and Day <= 15, Dual('0-5 Days',3),  Dual('',4)))) as Bucket;

Load [Created Date], Day([Created Date]] as Day From Table;

[its_anandrjs]

Use class function

LOAD *,

Class(Created Date, 5, 'Days') As DaysBucket;

From Location;

Regards

Anand

[abc_18]

Hi Anil,

I tried this expression, what is for Dual('',4) ??.

is it for >15 days??

[Anil_Babu_Samineni]

Yes

[abc_18]

Can I rename it like >15 Days??

because in bar chart for label where data is greater thn >15 is displaying blank

[Anil_Babu_Samineni]

Try this?

Load *, If(Day >=0 and Day <= 5, Dual('0-5 Days',1),  If(Day >=6 and Day <= 10, Dual('6-10 Days',2),  If(Day >=11 and Day <= 15, Dual('0-5 Days',3),  Dual('>15 Days',4)))) as Bucket;

Load [Created Date], Day([Created Date]] as Day From Table;

[effinty2112]

Hi Shika,

I think you want to have buckets for time intervals rather than dates. Such an interval (let's call it DateDiff) might be

DateDiff = Interval(Today() - Date(Timestamp#('3/18/2013  10:38:11 PM','M/D/YYYY  hh:mm:ss TT')),'D') giving 1617 days for this particular date.

Now we can put them into your buckets:

DateBucket =

Pick(

Match(-1,

DateDiff<=5,

DateDiff<=10,

DateDiff<=15,

-1),

'0-5 Days','6-10 Days','11-15 Days','>15 Days')

Cheers

Andrew

[Vijayqlik4171]

i need 1-100 days

100-300 days

300-500 days

500 to max

I have invoice date 

How to create bucket wise