topic Re: Date Help in QlikView

Date Help

Anonymous — Wed, 06 Apr 2016 18:32:10 GMT

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

Re: Date Help

sunny_talwar — Wed, 06 Apr 2016 18:34:06 GMT

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

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

Re: Date Help

swuehl — Wed, 06 Apr 2016 18:35:49 GMT

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

The Master Calendar

Re: Date Help

Anonymous — Wed, 06 Apr 2016 19:58:29 GMT

Guys. Thanks again. Very helpful.

Re: Date Help

MarcoWedel — Wed, 06 Apr 2016 21:59:17 GMT

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

Re: Date Help

MarcoWedel — Wed, 13 Apr 2016 20:02:45 GMT

You're welcome.

please close your thread if your question is answered.

thanks

regards

Marco