topic Re: Date bucket in QlikView

Date bucket

abc_18 — Tue, 22 Aug 2017 07:04:00 GMT

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" .

Re: Date bucket

Anil_Babu_Samineni — Tue, 22 Aug 2017 07:09:05 GMT

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;

Re: Date bucket

its_anandrjs — Tue, 22 Aug 2017 07:15:28 GMT

Use class function

LOAD *,

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

From Location;

Regards

Anand

Re: Date bucket

abc_18 — Tue, 22 Aug 2017 07:18:16 GMT

Hi Anil,

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

is it for >15 days??

Re: Date bucket

Anil_Babu_Samineni — Tue, 22 Aug 2017 07:19:39 GMT

Yes

Re: Date bucket

abc_18 — Tue, 22 Aug 2017 07:21:50 GMT

Can I rename it like >15 Days??

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

Re: Date bucket

Anil_Babu_Samineni — Tue, 22 Aug 2017 07:23:26 GMT

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;

Re: Date bucket

effinty2112 — Tue, 22 Aug 2017 07:48:00 GMT

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

Re: Date bucket

Vijayqlik4171 — Mon, 16 Aug 2021 10:26:34 GMT

i need 1-100 days

100-300 days

300-500 days

500 to max

I have invoice date

How to create bucket wise