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