help with building a calendar for 2 dates....

[Mario1011]

If there is one area I consistently struggle with, it's the concept of the Master Calendar and how to build it..

I'm asking for a Dummies Guide and script to building a single calendar for 2 date fields.

The Year STARTS on 1 July 2019 and ends 30 June 2020. It will be for month-end reporting, so I will never use today() in an expression. To get around this, I have created a calendar in Excel but would prefer to learn how to do this properly.

I will need to report on Current Month and Year to Date in the same table,etc ..... and should always reflect the current MTD and Year-to-Date to that month based on the user month selection

I would appreciate some knowledge being shared. I am trying to get the company to see the value of Qlik, so, unfortunately, I am currently using a desktop version, and don't have the resources left to open any new Qvw's

Table 1

Position ID	Cost Centre	Employee	Type	POSITION_DESC	Budget	Date
60079310	4170	70076	FTE	ASSESSOR	7,127.70	1/07/2019
60091296	4057	70323	FTE	SAFETY & WELLBEING TRAINER	7,716.61	1/07/2019
60090904	4017	70390	FTE	GENERAL MANAGER CULTURAL DEVELOPMENT	18,497.90	1/07/2019
60079299	4170	70562	FTE	MODERATOR	8,473.38	1/07/2019

Table 2

Cost Center	Name	Employee	Name	Cost Element	Cost Element Text	Wage Type	Wage Type Text	Posting Date	Hours	Amount
1221	Mgr NZPB Suppt Serv	80787	Te Nana K.	100	SALARIES - STAFF	1000	Annual Base Salary	1/07/2019	24	1,474.40
1221	Mgr NZPB Suppt Serv	80787	Te Nana K.	100	SALARIES - STAFF	1000	Annual Base Salary	10/07/2019	64	3,931.72
1221	Mgr NZPB Suppt Serv	80787	Te Nana K.	100	SALARIES - STAFF	1000	Annual Base Salary	24/07/2019	80	4,914.66
1221	Mgr NZPB Suppt Serv	80787	Te Nana K.	100	SALARIES - STAFF	3000	Annual Leave	10/07/2019	16	982.93
1221	Mgr NZPB Suppt Serv	80787	Te Nana K.	100	SALARIES - STAFF	3000	Annual Leave	24/07/2019	0	0

[Vegar]

First you'll need to model your data,  defining  and creating the the link between  the two tables. By the look of it, CC, employee and (Posting) Date are good candidates for a link. 

If you are able to link the data with the link fields then you only need one master calendar in your application. 

If not then you will need to explain how you are thinking to do your data modelling between the two tables.

[Mario1011]

my app is working fine with the timetable i created in excel. All links are
fine.... I'm wanting someone to teach me how to create a calendar in the
script and use my data as an example...

[Vegar]

Could you post a sample of your excel-calendar? 

[marcus_sommer]

From your description I'm not sure that you have really two different dates - yes originally they come from different tables and may be named differently - but they seem to have the same context.

One is the date within the fact-table of the actuals and the other is date from the fact-table of the budget-data - this means it's the same date in the sense what it's used for - comparing actual data against budget data. If this is the case you will need only one (simple) master-calendar for it. You need only multiple and/or advanced calendars if the dates have a different context like: orderdate --> deliverydate --> paymentdate or similar which depict some chain of events.

How to bring both dates together? It could be done like Vegar suggested by creating a key between both fact-tables - it's not really related to the date-fields else to the question how to relate the fact-tables per se. That's not always simple because quite often not all actuals have a budget and reversed. This means by a simple association you would always lose some information.
To avoid it you would either need to generate all missing keys on both sides and add them to the tables or you need to build a bridge- respectively link-table between the fact-tables.

Like already mentioned it's not always very easy so I suggest to apply a far more simple way just by concatenating both fact-tables into a single fact-table - you need only to rename the fields with the same content equally.

- Marcus

[Mario1011]

Thanks. Like i said , that's exactly what I've done. I was trying to get a
script to create the calendar in the load script instead of creating one in
excel.

I just renamed all date fields to "Date" to link to the calendar....
although i had to use set anaysis to force the value to read the current
month when no month selected in order to show both current MTD and YTD i
the same table.

It's just about me trying to build the knowledge as to how it's done.

[marcus_sommer · Accepted solution]

Is it really only the calendar-creation, that's simple:

cal:
load *, month(date) as Month, year(date) as Year;
load date('1 July 2019' + recno() - 1) as date
autogenerate '30 June 2020' - '1 July 2019';

Just a bit simplyfied - the used date-values must be to control the autogenerate or to be calculable pure numeric or be interpreted as vaild dates - that's all.

- Marcus