topic Re: how to create bucket for date in QlikView

how to create bucket for date

Priya_9 — Sat, 04 Jun 2022 05:11:01 GMT

Hi all,

please tell me how to create bucket for date.

i having data like this

from this i want to create bucket like

OrderDate

20-May-22

31-May-22

30-May-22

30-Apr-22

01-May-22

02-May-22

03-May-22

04-May-22

05-May-22

06-May-22

07-May-22

bucket

0-30days

30-60days

60-90days

>90days

kindly suggest me to create bucket where in scripting or dashboard

Re: how to create bucket for date

Vegar — Fri, 03 Jun 2022 18:56:53 GMT

First. You need to ensure that you can interpret the date values as date. If they aren't date values then you can do this: date#(OrderDate, 'DD-MMM-YY')

Second You need to identify your comparison date. I assume you want to compare with today's date You can use today()

Third: To get the no of days between your OrderDate and your comparison date use subtraction, like this:

today () - date#(OrderDate, 'DD-MMM-YY')

Fourth. Bucket your date diff. You can do this using an nested if statement or using interval match. For this example I'll use an nestled if statement like this.

IF(today () - date#(OrderDate, 'DD-MMM-YY')<=30, '0-30 days',

IF(today () - date#(OrderDate, 'DD-MMM-YY')<=60, '31-60 days',

IF(today () - date#(OrderDate, 'DD-MMM-YY')<90, '61-90 days','>90 days')))

Hope this was helpful in order for you to bucket your data.

-Vegar

Re: how to create bucket for date

Priya_9 — Mon, 06 Jun 2022 04:55:52 GMT

Thank you