Date Help

[Anonymous]

Hi All,

I need some help with creating new dates in the future. I was able to create one date but wanted to create more future dates. Such as

• Month End Date for the rest of the year (5/31/16; 6/30/16; etc)
• Quarter End Date (6/30/16; 9/30/16; 12/31/16)
• Year End Date (12/31/16; 12/31/17 etc)

My thought is a For Loop inside this:

Date#(date(now(),'MM/DD/YYYY')) as New_Date,

Any inputs greatly appreciated!

Data:
LOAD Fund,
Company,
New_Date,
Amount,
FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate (Data)

Data2:
Load Distinct
Fund,
Company,
Date#(date(now()-1,'MM/DD/YYYY')) as New_Date,
0 as Amount,
Resident Data;

Many Thanks,

Frank

[sunny_talwar]

If you have master calendar, then you can do it while creating a master calendar.

LET vMax = AddYears(YearEnd(Today(), 1));

[swuehl]

I would also use a master calendar and set the max date to whatever value you like:

The Master Calendar

[Anonymous]

Guys. Thanks again. Very helpful.

[MarcoWedel]

Hi,

one example to create a calendar table including month, quarter and year end dates could be:

tabCalendar:
LOAD *,
    Day(Date) as Day,
    WeekDay(Date) as WeekDay,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Month(Date) as Month,
    MonthName(Date) as MonthName,
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear,
    DayName(MonthEnd(Date)) as MonthEndDate,
    DayName(QuarterEnd(Date)) as QuarterEndDate,
    DayName(YearEnd(Date)) as YearEndDate;  
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD MakeDate(2016,1,1) as MinDate,
    MakeDate(2017,12,31) as MaxDate
AutoGenerate 1;

hope this helps

regards

Marco

[MarcoWedel]

You're welcome.

please close your thread if your question is answered.

thanks

regards

Marco