Date Buckets Issue

[#1]

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

[Gysbert_Wassenaar]

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];

[#3]

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

[kaushiknsolanki]

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

[jagan · Accepted solution]

Hi,

PFA file for solution.

Regards,

Jagan.

Celambarasan Adhimulam

[jagan]

Hi Srinu,

Is this working correctly?

Regards,

Jagan.