Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Good
Employee
Employee

Creating A Master Calendar

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;

78 Replies
userid128223
Creator
Creator

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
Employee
Employee
Author

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
Creator
Creator

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Employee
Employee
Author

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
Creator
Creator

Can you please explain the below statement

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

thanks

userid128223
Creator
Creator

Thanks Josh & Rob for your help.

simongoodman
Creator
Creator

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

simongoodman
Creator
Creator

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 <