Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Employee
Employee

Re: Creating A Master Calendar

Thanks Iain!  I have updated the script in the original post.

-Josh

MVP & Luminary
MVP & Luminary

Re: Creating A Master Calendar

I think a better solution may be to use the WeekYear() function.

WeekYear(tempdate)

will return 2011.

I only know about this because I made the same mistake in the Qlikview Components calendar and someone sent me a correction.

-Rob

Employee
Employee

Re: Creating A Master Calendar

Thanks Rob.  Sometimes I'm amazed at the number of functions we have to in QV!  I have updated the script.

-Josh

userid128223
Contributor

Re: Creating A Master Calendar

Hi Rob

So in my case I am doing master calander where fiscal starts from sep to Aug. I have alligned quarters accordingly for inline. How do I make changes to fiscal year.

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q2

2, Q2

3, Q3

4, Q3

5, Q3

6, Q4

7, Q4

8, Q4

9, Q1

10, Q1

11, Q1

12, Q2

];

What should be the change to below code:

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

Should it be adjusted to.below?

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

Do I need to make any other change for my Calander to function.

Employee
Employee

Re: Creating A Master Calendar

My recommendation is to use the AddMonth Function.

Year(AddMonth(TempDate,3)) as FiscalYear

This will take a date in Sept 2012 and return 2013.  If you wish you can add this line to the script for the MasterCalendar table and retain the line that just has the calendar year so you will be able to filter on Fiscal Year and/or Calendar Year.

You may also want to add line for the fiscal month number but I would also retain the calendar month.  This will enable you to sort or the Month field in the correct fiscal order (i.e. sept first)

....

Month(TempDate) As Month,

Num(Month(AddMonth(TempDate,3))) as FiscalMonthNumber,

....

Not applicable

Re: Creating A Master Calendar

You can use below code, for getting quarters with differant months.

We can change order of months and quarters in the way we needed.

Mapinline:

mapping Load * inline [month,Quarter

1,Q1

2,Q1

3,Q1

4,Q2

5,Q2

6,Q2

7,Q3

8,Q3

9,Q3

10,Q4

11,Q4

12,Q4];

Not applicable

Re: Creating A Master Calendar

Hi I am truing to implement the master calendar but I have to load data from two tables in an access file one containing dates from 2011 and the other from 2012.

I use the following code, but I only get the 2011 data to apear, what should I change?

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\t42386\Documents\GR Data\ Pick Detail 2011-12.accdb];

OrderHeader:

LOAD Date,

    Material,

    `Pallet_ID`,

    `Picked Cases`,

    PickType,

    `Plant_NO`,

    Route;

SQL SELECT *

FROM ` Pick Detail 2011`;

OrderHeader:

LOAD Date,

    Material,

    `Pallet_ID`,

    `Picked Cases`,

    PickType,

    `Plant_NO`,

    Route;

SQL SELECT *

FROM `Pick Detail 2012`;

Employee
Employee

Re: Creating A Master Calendar

Hi,

If you load two tables into QV that have exactly the same fields in them, then QV will automatically concatenate the tables together. The 2012 and the 2011 data will be in the same table. From there you should be good to follow the example.

-Josh

Sent from my Commodore 64

Not applicable

Re: Creating A Master Calendar

I fixed it with

SQL SELECT *

FROM `Grand Rapids Pick Detail 2011`

UNION ALL

SELECT *

FROM `Grand Rapids Pick Detail 2012`;

Thanks

qlikconsultant
Contributor II

Re: Creating A Master Calendar

Nice Calendar, but I have a little bit of troubel to configure the skript to make the calendar minute based.