Qlik Community

QlikView Documents

Documents for QlikView related information.

Buckets creation with future and past dates

MVP & Luminary
MVP & Luminary

Buckets creation with future and past dates

Hope attached file helps in creating buckets dimension for a date field having future and past dates.  The attached file especially helpful when your Date Dimension has both past and future dates and you need to create Buckets for the Dates.  The buckets should be like Future> 0-30, Future> 30 and 60 etc, and for past dates the buckets like PAST 0-30, PAST>30-60 etc.  Please find the script below, it is achieved with multiple If()

Let vToday = num(Today());

Data:
LOAD
*,
if(DaysDifference < -120,'Future > 120',
if(DaysDifference < -60 AND DaysDifference >= -120, 'Future_>60AND<=120',
if(DaysDifference < -30 AND DaysDifference >= -60, 'Future_>30AND<=60',
if(DaysDifference <= 0 AND DaysDifference >= -30, 'Future_>0-30',
if(DaysDifference > 120, 'PAST>120',
if(DaysDifference > 60 AND DaysDifference <= 120, 'PAST_>60AND<=120',
if(DaysDifference > 30 AND DaysDifference <= 60, 'PAST_>30AND<=60',
if(DaysDifference > 0, 'PAST_0-30', 'N/A')))))))) AS Bucket;    
LOAD  Due_Date,
$(vToday)-floor(num(Due_Date)) AS DaysDifference,
     If($(vToday)-floor(num(Due_Date)) > 0, 1, 0) AS IsPastDate,
      Order_id,
      Order_Line_item_id
FROM Due_Dates.xls
(biff, embedded labels, table is [Sheet1$]);

Regards,

Jagan.

Attachments
Comments
Employee
Employee

Can you provide some documentation to explain this QVW, what techniques you use, when you might apply this logic?

Thanks

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-08-24 05:30 AM
Updated by: