Calendar Help

[leocattqv]

Hi All,

So I have a Calendar that should be working,  the Calendar table is matched up with the main data table, but I am not getting a relationship with the fields

I have attached an example - does anyone have ideas?

[Digvijay_Singh]

You need to populate your period field properly in fact table, it is showing month names currently but calendar table data looks ok.

[Anonymous]

The fields have the same name but they don't have matching values.

In your Complaints qvd load try changing this

Month([Date Originated])as Period

to

date([Date Originated],'YYYYMM') as Period,

[vishsaggi]

Try this:

In your actual Fact table Load script where you have this field Period. Add like

Load .....,

        Num(Floor(Period))                                 AS %Key_Date,

        .......

FROM yoursource;

// Calendar Script Revamped *************************************************

MasterCalendar:

Load

Num(Floor(TempDate))                                 AS %Key_Date,

//Date(TempDate, 'MM/DD/YYYY')                AS PeriodDate,

'W' & week(TempDate)                                 AS Week,

Year(TempDate)  AS Year,

Month(TempDate)  AS Month,

Day(TempDate)  AS Day,

YeartoDate(TempDate)*-1                              AS CurYTDFlag,  

YeartoDate(TempDate,-1)*-1                           AS LastYTDFlag,  

//inyear(TempDate, Monthstart($(varMaxDate)),-1)       AS RC12,  

//date(monthstart(TempDate), 'MMM-YYYY')               AS MonthYear,   

'Q' & ceil(month(TempDate) / 3)                      AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

WeekDay(TempDate)                              AS WeekDay

;

//=== Generate a temp table of dates ===

LOAD

      date(mindate + IterNo()) AS TempDate,

      maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Period', recno()))-1 AS mindate,

max(FieldValue('Period', recno()))   AS maxdate

AUTOGENERATE FieldValueCount('Period');