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.