Date buckets ....

[#1]

Hi All,

I have PO_Date which is in format of  7/18/2013  12:04:59 PM.  Based on today date i need to create buckets for the dates that have passed 

'PAST_0to30' , 'PAST_>30 and <=60' and "PAST_>60"  .

Please find attached spreadsheet

Thanks

[Gysbert_Wassenaar · Accepted solution]

Let vToday = num(Today());
Table1:
LOAD po_line_item_id, 
     po_id, 
     qty, 
     PO_Date,
     if($(vToday)-PO_Date<=30,
        'PAST_0to30',
        if($(vToday)-PO_Date<=60,
           'PAST_>30 and <=60',
           'PAST_>60')
     ) as Bucket
FROM
Buckets.xls
(biff, embedded labels, table is [Sheet1$]);

See attached example.

[jagan]

Hi,

Try this

Test:

LOAD

*,

          If(DaysDiff > 0 AND DaysDiff < 30, Dual('0-30', 1),

                    If(DaysDiff >= 30 AND DaysDiff < 60, Dual('30-60', 2),

                    If(DaysDiff >= 60, Dual('>=60', 3), 'N/A'))) AS Buckets;

LOAD po_line_item_id,

     po_id,

     qty,

     Date(PO_Date) AS PO_Date,

     Ceil(Num(Today()) - Num(PO_Date)) AS DaysDiff

FROM

C:\Users\hi\Desktop\Buckets.xls

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

Hope this helps you.

Regards,

Jagan.