Creating A Master Calendar

[Josh_Good]

This videos show how to create a Master Date Calendar in QlikView.  The script mentioned in the video is below.

http://youtu.be/ScdIQvWzVFs

QuartersMap:
MAPPING LOAD 
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
               min(OrderDate) as minDate,
               max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
               TempDate AS OrderDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[userid128223]

Thanks Josh. I tried your script it gave me error.

"Generic tables must contain at least 3 fields"

Rob.

I have tried using calander from QV components. However how do you adjust quarter to start at different month using components.

thanks

[Josh_Good]

Sorry about that.  I failed to name the second column in the inline load.   I have corrected it in my previous post.

-Josh

The bold is what was added:

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

[userid128223]

Thanks Josh.

It works. however when i am getting weird dates.

19981203    (YYYYMMDD)   Source

however when i bring that date and try passing thru date function, i get weird numbers.

year(saledate)as year,

I get numbers that is unlike year.

56606

Due to this my master calander function is not working properly.

[rwunderlich]

If you want to offset your entire Calendar -- a fiscal calendar -- set the FirstMonth parm to 12 in your call to Qvc.Calendar or Qvc.CalendarFromField. For example:

CALL Qvc.CalendarFromField('OrderDate', 'Fiscal Calendar', 'Fiscal ', '12');

If instead you want a standard calendar, but want to offset just the Quarter field, use the new code extension feature available in Qvc 4.0.

SET Qvc.Global.Extension.Directory=C:\local\QvcExtensions;

and the contents of C:\local\QvcExtensions\CalendarExtFields.qvs would be:

,'Q' & Ceil (month(AddMonths(Date,1)/3) as [$(_fieldPrefix)OffsetQuarter]

If you want to replace the standard Quarter field with the offset one, follow the calendar generation with:

DROP FIELD Quarter;

RENAME FIELD OffsetQuarter TO Quarter;

-Rob

[Josh_Good]

I believe the issue is your dates are not in a format that QlikView is expecting you can either change the default format or change the date to a format QlikView is expecting.

To change the default format you need to change the statement that sets this.  This is typicaly line 7 on the first tab of the script.

e.g. change  SET DateFormat='M/D/YYYY';  to SET DateFormat='YYYYMMDD';

Alternatively (my preference), change the format of the source data by parsing out the date into its components and putting it back together when you load it.  The expression would look something like this:

MakeDate(Left(SourceDate, 4), Right(Left(SourceDate,2),6), Right(SourceDate,2)) as Date

[userid128223]

Can you please explain the below statement

If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

thanks

[userid128223]

Thanks Josh & Rob for your help.

[simongoodman]

I have just seen this and I am in the process of creating a fiscal calendar.

How did you get CurYTDFlag and LastYTDFlag to start at the beginning of the fiscal year? I have tried a couple of things unsucessfully.

Similarly how did you get Week to realign with the fiscal months? Or how did you create a fiscal week in the script?

[rwunderlich]

Creating fiscal fields is all about using the FirstMonthOfYear parm in the various date functions. For example, see the help for InYearTodate().

-Rob

[simongoodman]

Hi Rob

Thanks for that. I understand the concept but failed in execution. I think

I can not see the wood for the trees. 1 is as per Help and 2 is one of my

failed efforts.

1. inyeartodate(date, basedate , shift )

2. inyeartodate(TempDate, $(varToday), 0 )

• -1 AS CurYTDFlag

Please can you help?

Simon

On Tue, Jun 12, 2012 at 3:17 PM, Rob Wunderlich <