Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY

Buckets creation with future and past dates

cancel
Showing results for 
Search instead for 
Did you mean: 
jagan
MVP
MVP

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
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
Last update:
‎2013-08-24 05:30 AM
Updated by: