topic Re: Date Buckets Issue in QlikView

Date Buckets Issue

Thu, 22 Aug 2013 18:21:33 GMT

Hi All,

I have a date filed by name "Due_Date" I need to create buckets based on Today() and Due Date.

All date are based on Today (i.e 08/22/2013 = Today())

If due date is between .

08/22/2013 - 07/22/2013 (0 and <=30) Bucket Name is "PAST_0-30"

07/23/2013 - 05/23/2013 (>30 and <=60) Bucket Name is "PAST_>30AND<=60"

05/24/2013 - 03/23/2013(>60 and <=120) Bucket Name is "PAST_>60AND<=120"

Before 03/24/2013 (>120) Bucket Name is "PAST>120"

08/23/2013 - 09/23/2013 (0 and <=30) Bucket Name is "Future_>0-30"

09/23/2013 - 11/23/2013 (>30 and <=60) Bucket Name is "Future_>30AND<=60"

11/24/2013 - 01/23/2014(>60 and <=120) Bucket Name is "Future_>60AND<=120"

After 01/23/2014 (>120) Bucket Name is "PAST>120".

I am able to get only past Buckets but cant get future buckets ... Please find attached Qvw and Excel File

Appreciate help on this ... Thanks

Re: Date Buckets Issue

Gysbert_Wassenaar — Thu, 22 Aug 2013 18:32:53 GMT

Try this:

Let vToday = num(Today());

Data:

LOAD Due_Date,

if($(vToday)-floor(num(Due_Date)) >75,'4',

if($(vToday)-floor(num(Due_Date)) <-30,'6',

if($(vToday)-floor(num(Due_Date)) <0,'5',

if($(vToday)-floor(num(Due_Date)) <=30,'1',

if($(vToday)-floor(num(Due_Date)) <=60,'2',

if($(vToday)-floor(num(Due_Date)) <=75,'3','6')))))) as BucketsOrder,

Order_id,

Order_Line_item_id

Due_Date.xls

(biff, embedded labels, table is [Sheet1$]);

Buckets_Sort:

load * inline [

BucketsOrder, Buckets

1, Past_0-30

2, Past_>30AND<=60

3, Past_>60AND<=75

4, Past_>75

5, Future_0-30

6, N/A];

Re: Date Buckets Issue

Thu, 22 Aug 2013 18:39:53 GMT

Hi Wassenaar,

The logic seems to be not working for example when i select any date in year 2012 its falling in the bucket Future0-30.

It supposed to come under past >75

Thanks

Re: Date Buckets Issue

kaushiknsolanki — Thu, 22 Aug 2013 19:55:31 GMT

Hi,

I checked the application shared by the G Wassenaar, and its working fine.

I can select any date of year 2012 and it falls under past > 75 bucket.

Kindly give a specific date which you have noticed.

Regards,

Kaushik Solanki

Re: Date Buckets Issue

jagan — Sat, 24 Aug 2013 09:04:50 GMT

Hi,

PFA file for solution.

Regards,

Jagan.

Celambarasan Adhimulam

Re: Date Buckets Issue

jagan — Mon, 26 Aug 2013 02:37:50 GMT

Hi Srinu,

Is this working correctly?

Regards,

Jagan.