Issues with Master Calendar

[#1]

Hi All,

I posted a question about this about two weeks ago and then updated the question but I think I may have caused confusion so I'd like to start over.

I am designing in Qlik Sense a "multi-category" app. It's for a Restaurant group which has a number of sites and I'm pulling together costs in categories like Electricity, Merchant services, telecoms etc... The only common factor and association between all of these is "Sites". Each fact table has dates. Ideally, I'd like to be able to filter on a Master Date (say Month) and see the values for each of these categories by month and by site. I know I need a Master Calendar but I can't seem to create one despite numerous attempts. 

I attach my qvf file as it stands at present

I hope someone can help me.

Best wishes,
Denis

[petter]

This is a must-read article for anyone struggling with dates and multiple dates:

Canonical Date‌ and Why You sometimes should Load a Master Table several times

There is a overview page linking to these and other material on Community here:

How to use - Master-Calendar and Date-Values

[girirajsinh]

Hi Denis

You may try following. This is I have been using for my applications.

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -2)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

Calendar:
LOAD
     CALENDAR_DATE AS %KEY_CALENDAR_DATE,
     DATE(CALENDAR_DATE) AS Date,
     YEAR(CALENDAR_DATE) AS Year,
     MonthName(date(CALENDAR_DATE)) as MonthName,
     MONTH(CALENDAR_DATE) as MonthShort,
     DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS Month,
     NUM(MONTH(CALENDAR_DATE)) AS Month_num,
     NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS YYYYMM,

//    DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS YYYYMMDD,
    DAY(CALENDAR_DATE) AS Day,
//    DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS Weekday,
    'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS Quarter,
//    DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
//         NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
//    DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
        YEAR(WEEKSTART(CALENDAR_DATE)) &  NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00') AS WeekC

;
LOAD
     ($(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

[MK9885]

Your Data Model is bit messy.

Which is the fact table in all those tables? Seem like they all are Dim Tables.

Site_ID seem to be a field which can be key to other Dim Tables.

If the no of records are not many and if fields have same data then join/concatenate them to create a Fact Table.

Then Select a Date field from Fact Table to make a Key to your Master Table.

If not confidential and if possible can you also attach the xl files here?

Anyway I've inserted the Master Calendar script in attached file. But to use that you need to join it with a Date Key or DateID.

I suggest create a Fact, join it to Master Calendar based upon/selecting one date as DateID and use other Dim tables to key it with Fact.

Thanks.