The best way that I find is to concatenate all fact tables into a single table, and rename and augment columns as required. This tends to work much better than having lots of table.
So from Sales:
'Sales' as RowType,
[Sales Date] as Date
Then from Order:
'Order' as RowType,
[Order Date] as Date
As you don't have Group in your Order table, when you select a Group you will not see any orders (is this correct?). This can be fixed either by looking up the group when you load orders, or by ignoring selections on Group using Set Analysis when looking at Order values.
If you have large amounts of data and QVDs you will need to ensure you make your loads Optimised. This can be done by keeping columns in all concatenated tables identical.
Other techniques for multiple fact tables and a single calendar are Link Tables and putting your date table in a Data Island.
Hope that all makes sense and is of help.
I have simplified the Question and app to go step by step. Pls see the attached QVW.
In the app, 1st table is how many products were sold in 2015? ans sud be 4. why is it showing 6?
I think I have the link table correctly place with Type as Sales which is used in the expn.
Pls comment what I am missing.
Try this one
- MAPPING LOAD
- rowno() as Month,
- 'Q' & Ceil (rowno()/3) as Quarter
- AUTOGENERATE (12);
- min(OrderDate) as minDate,
- max(OrderDate) as maxDate
- Resident Orders;
- Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
- Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
- DROP Table Temp;
- $(varMinDate) + Iterno()-1 As Num,
- Date($(varMinDate) + IterNo() - 1) as TempDate
- AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
- TempDate AS OrderDate,
- 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,
- ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
- Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
- WeekDay(TempDate) as WeekDay
- Resident TempCalendar
- Order By TempDate ASC;
- Drop Table TempCalendar;