One Calendar for multiple dates

Fri, 30 Sep 2016 00:13:39 GMT

I have an application that measures the different performance factors throughout the various sectors of our business which we term "metrics". Each one is very different from another, so I've pulled them in with different loads. I have the need to be able to select one fiscal year (Calculated by a central Master Calendar) for all the different "metrics" in my application. Each metric (or table) has a unique date field which works if I make them separate calendars, however I'm at a loss trying to find how to make them work from a single "fiscal year" calendar rather than multiple calendars. Can you tell me how I can create a calendar that allows user to choose the date (fiscal year) only once for all the metrics? The date fields are all named differently at this point in time. I am also enclosing the script that creates the calendars here. You'll note that I sometimes have two calendars (fiscal year (starting month 7) and program year (starting month 4). The metric name is the same value for every table. Do I need to auto generate a unique ID first?

Relational Diagram:

The script I am using for each metric is the same, as follows:

SET vFiscalYearStartMonth = 7;

LET vStartDate = Num(YearStart(Today(), -6));

LET vEndDate = Num(YearEnd(Today()));

TempCalendar:

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS OpenDate,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

CalendarTemp:

LOAD

'FS 1.06' as 'FS106_MetricName',

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(OpenDate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD *,

Year(OpenDate) AS Year, // Standard Calendar Year

Month(OpenDate) AS Month, // Standard Calendar Month

Date(MonthEnd(OpenDate), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(OpenDate)/3), Ceil(Month(OpenDate)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(OpenDate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(OpenDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear // Fiscal Calendar Year

Resident TempCalendar;

MasterCalendar_FS106_FY:

LOAD

FS106_MetricName,

Date(OpenDate) as FS106FY_SVC_OUTCOME_DATE,

RowNum as FS106FY_RowNumber,

Year as FS106FY_CalYear,

Month as FS106FY_CalMonth,

MonthName as FS106FY_CalMonthName,

Quarter as FS106FY_CalQuarter,

FiscalMonth as FS106FY_FiscalMonth,

FiscalYear as FS106FY_FiscalYear,

FiscalQuarter as FS106FY_FiscalQuarter,

FiscalMonthName as FS106FY_FiscalMonthName

Resident CalendarTemp;

DROP TABLE TempCalendar;

DROP TABLE CalendarTemp;

I would appreciate your help in solving this, Thank you!

topic One Calendar for multiple dates in QlikView

One Calendar for multiple dates