Data model - avoiding a loop

[twanqlik]

I want to connect 2 fact tables to one Calender and to 1 dimension, however i'm not able to do that without creating a loop.

I've attached my data model, i think i did something wrong in my data model

Basically what i want:

1) Filter on source_system_code

Result: data from 'Fact_ConsumerRegistrations' + data from 'Sell_Out data'

2) Filter on data:

Result: data from 'Fact_ConsumerRegistrations' + data from 'Sell_Out data'

Currently i'm able to do the first part, but not the second part.

[vishsaggi]

No harm in creating a multi calendar (i mean another calendar) in your data model and link it to your sell out data table.

Try this and see.

MasterCalendar:

Load

Num(Floor(TempDate))                                   AS %PROD_KeyReportDate,

week(TempDate)                                         AS PROD_Week,

Year(TempDate)                                         AS PROD_Year,

Month(TempDate)                                        AS PROD_Month,

Day(TempDate)                                          AS PROD_Day,

YeartoDate(TempDate)*-1                                AS PROD_CurYTDFlag,  

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

inyear(TempDate, Monthstart($(varMaxDate)),-1)         AS PROD_RC12,  

date(monthstart(TempDate), 'MMM-YYYY')                 AS PROD_MonthYear,   

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

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

WeekDay(TempDate)                                      AS PROD_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('ReportDate', recno()))-1        AS mindate,

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

AUTOGENERATE FieldValueCount('ReportDate');

//**************************************************************************/

And Create a keyreportdate in your selloutdata table like

SELL_OUT_DATA:

LOAD yourcolumns,

          Num(Floor(ReportDate))   AS PROD_KeyReportDate

FROM yourdatasourcetable;