Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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" .
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;
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;
Use class function
LOAD *,
Class(Created Date, 5, 'Days') As DaysBucket;
From Location;
Regards
Anand
Hi Anil,
I tried this expression, what is for Dual('',4) ??.
is it for >15 days??
Yes
Can I rename it like >15 Days??
because in bar chart for label where data is greater thn >15 is displaying blank
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;
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
i need 1-100 days
100-300 days
300-500 days
500 to max
I have invoice date
How to create bucket wise