Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Buckets creation with future and past dates

jagan
Not applicable

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
Qlik_Trigg
Not applicable

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

Thanks

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