Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!