topic Re: Date buckets .... in QlikView

Date buckets ....

Mon, 12 Aug 2013 14:30:57 GMT

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

Re: Date buckets ....

Gysbert_Wassenaar — Mon, 12 Aug 2013 14:38:56 GMT

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.

Re: Date buckets ....

jagan — Mon, 12 Aug 2013 14:39:35 GMT

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.