Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Buckets creation with future and past dates

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

Buckets creation with future and past dates

Attachments

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.

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: