Master Calendar issue

[#1]

Hi Guys,

I am stuck in defining the Min and Max date (last load statement) while making the master calendar. Someone an idea?

My Excel  files shows dates as follows: "December 2016 Final" (this is one cell)

-------------------------------------------------------------------

RepMonth:

Load Date(Date#(SubField("[$C].[Reporting Date]",' ',1),'MMMM'),'MMM') as Month,
SubField("[$C].[Reporting Date]",' ',2) as Year

from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);


MasterCalendar:

Load Date("[$C].[Reporting Date]") AS [Reporting Month],
Year("[$C].[Reporting Date]") AS Year,
'Q'& Ceil (Month(("[$C].[Reporting Date]")) / 3) AS Quarter,
Month("[$C].[Reporting Date]") As Month,
Day("[$C].[Reporting Date]") As Day,
Week("[$C].[Reporting Date]") As Week;

Load
Date(MinDate + IterNo() -1 ) As "[$C].[Reporting Date]"
While (MinDate + IterNO() -1) <= Num(MaxDate);

Load
Min(date(Date#("[$C].[Reporting Date]"))) as MinDate,
Max(date(Date#("[$C].[Reporting Date"))) as MaxDate
RESIDENT RepMonth ;

-------------------------------------------------------------------

[prieper]

because this field is not loaded. please check your tables.

[prieper]

I am usually using a subroutine for the mastercalendar:

//    ==== Create a calendar on basis (full) days for the entire period ==================================================================
    SUB MakeCalendar (sDateField, sCalendarName)
        $(sCalendarName):
            LOAD
                dtDate                                                    AS [$(sDateField)],
                YEAR(dtDate)                                            AS [$(sDateField)_Y],
                DUAL('Q' & CEIL(MONTH(dtDate) / 3),
                    CEIL(MONTH(dtDate) / 3))                            AS [$(sDateField)_Q],
                MONTH(dtDate)                                            AS [$(sDateField)_M],
                //WEEK(dtDate)                                            AS [$(sDateField)_W],
                //DAY(dtDate)                                                AS [$(sDateField)_D],
                //WEEKDAY(dtDate)                                            AS [$(sDateField)_Day],
                MONTHNAME(dtDate)                                        AS [$(sDateField)_YM],
                //WEEKNAME(dtDate)                                        AS [$(sDateField)_YW],
                YEAR(dtDate) * 12 + NUM(MONTH(dtDate))                    AS [$(sDateField)_Mn];            // running monthes for easy SET-Analysis            
            LOAD    // Create a List of all Dates betw Min and Max
                DATE(MinDate + ITERNO())                                AS dtDate
            WHILE
                MinDate + ITERNO() <= MaxDate;        
            LOAD    // Extract the Min/Max-Values for the dates
                MIN(FIELDVALUE('$(sDateField)', RECNO())) -1            AS MinDate,
                MAX(FIELDVALUE('$(sDateField)', RECNO())) +0            AS MaxDate
            AUTOGENERATE
                FIELDVALUECOUNT('$(sDateField)');        
    END SUB

This subroutine then might be called by

CALL MakeCalendar ('DateField', 'CalendarName');

HTH Peter

[#4]

Didn't I loaded it in above statement?

[prieper]

Please check your datamodel.

With the script:

RepMonth:
  Load Date(Date#(SubField("[$C].[Reporting Date]",' ',1),'MMMM'),'MMM') as Month,
SubField("[$C].[Reporting Date]",' ',2) as Year
  from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);

You will have only two fields in the table RepMonth: Month and Year, but surely not the field containing the date.

Peter

[#6 · Accepted solution]

Was able to fix it yesterday:

I've changed the code into the following:

///////////////////////////////////////////////////////////////////////////////

RepMonth:

Load "[$C].[Reporting Date]" As [Reporting Month]

from [..\Data Files\HR-Cube\*.xlsx]
(ooxml, embedded labels);


MasterCalendar:

Load Date("[$C].[Reporting Date]") AS [Reporting Month],
Year("[$C].[Reporting Date]") AS Year,
'Q'& Ceil (Month(("[$C].[Reporting Date]")) / 3) AS Quarter,
Month("[$C].[Reporting Date]") As Month;

Load
Date(MinDate + IterNo() -1 ) As "[$C].[Reporting Date]"
While (MinDate + IterNO() -1) <= Num(MaxDate);

Load
Min (Date(Date#(SubField([Reporting Month],' ',1),'MMM'),'MMM')) as MinDate,
Max (Date(Date#(SubField([Reporting Month],' ',1),'MMM'),'MMM')) as MaxDate,
Min (Date(Date#(SubField([Reporting Month],' ',2),'YYYY'),'YYYY')) as MinDate,
Max (Date(Date#(SubField([Reporting Month],' ',2),'YYYY'),'YYYY')) as MaxDate



RESIDENT RepMonth;
///////////////////////////////////////////////////////////////////////////////

[prieper]

and?

does it work?

[#8]

yes, the code works