Hi everyone can one help regarding the data modelling of two different calendars.
hi in my requirement i need to get all date calculations common for two different tables.
one table have 2014,2015,2016 date and second table have 2015,2016 date only, when i do concatenation of two tables it working for reports well but when i click on the salefy it default showing the second table months only not first table months.
please see the data below...
SS_OrdersCalTemp:
Load Min(BILLDATE) as SS_MinOrdDate,
Max(BILLDATE) as SS_MaxOrdDate
Resident CLOSINGSTOCKSUMMARY;
let SS_EndDate=Date(Peek('SS_MaxOrdDate',0,'SS_OrdersCalTemp'),'$(DateFormat)');
let SS_StartDate=Date(Peek('SS_MinOrdDate',0,'SS_OrdersCalTemp'),'$(DateFormat)');
let SS_caldays=(SS_EndDate-SS_StartDate)+1;
let SS_vToday=num(SS_EndDate);
SS_Cal:
Load RecNo() as SS_RecordNo,
if(RecNo()=1,Date('$(SS_StartDate)'),Date(peek("SS_D")+1)) as SS_D
autogenerate(SS_caldays);
SS_Calendar:
LOAD
SS_D AS BILLDATE,
Floor(SS_D) as NumDate,
SS_D AS Date,
Year(SS_D) AS Year,
Month(SS_D) AS Month,
week(SS_D) AS Week,
weekday(SS_D) AS Weekday,
Week(SS_D) & '-' & Year(SS_D) AS WeekYear,
day(SS_D) AS Day,
date(SS_D, 'MM/DD') AS DateMMDD,
if(Month(SS_D)=4 or Month(SS_D)=5 or Month(SS_D)=6,1,
if(Month(SS_D)=7 or Month(SS_D)=8 or Month(SS_D)=9,2,
if(Month(SS_D)=10 or Month(SS_D)=11 or Month(SS_D)=12,3,
if(Month(SS_D)=1 or Month(SS_D)=2 or Month(SS_D)=3,4)))) AS Quarter,
IF(MONTH(SS_D)> 4 or MONTH(SS_D)= 4, YEAR(SS_D)+1, YEAR(SS_D)) as SalesFY,
if(Month(SS_D)=1,10, if(Month(SS_D)=2,11,if(Month(SS_D)=3,12,if(Month(SS_D)=4,1,
if(Month(SS_D)=5,2,if(Month(SS_D)=6,3,if(Month(SS_D)=7,4,if(Month(SS_D)=8,5,
if(Month(SS_D)=9,6,if(Month(SS_D)=10,7,if(Month(SS_D)=11,8,if(Month(SS_D)=12,9))))))))))))as SalesMonthOrder
Resident
SS_Cal;
OrdersCalTemp:
Load Min(BILLDATE) as MinOrdDate,
Max(BILLDATE) as MaxOrdDate
Resident BILLMASTER;
let EndDate=Date(Peek('MaxOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');
let StartDate=Date(Peek('MinOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');
let caldays=(EndDate-StartDate)+1;
let vToday=num(EndDate);
Cal:
Load RecNo() as RecordNo,
if(RecNo()=1,Date('$(StartDate)'),Date(peek("D")+1)) as D
autogenerate(caldays);
Concatenate(SS_Calendar)
Calendar:
LOAD
D AS BILLDATE,
Floor(D) as NumDate,
D AS Date,
Year(D) AS Year,
Month(D) AS Month,
week(D) AS Week,
weekday(D) AS Weekday,
Week(D) & '-' & Year(D) AS WeekYear,
day(D) AS Day,
date(D, 'MM/DD') AS DateMMDD,
if(Month(D)=4 or Month(D)=5 or Month(D)=6,1,
if(Month(D)=7 or Month(D)=8 or Month(D)=9,2,
if(Month(D)=10 or Month(D)=11 or Month(D)=12,3,
if(Month(D)=1 or Month(D)=2 or Month(D)=3,4)))) AS Quarter,
IF(MONTH(D)> 4 or MONTH(D)= 4, YEAR(D)+1, YEAR(D)) as SalesFY,
if(Month(D)=1,10, if(Month(D)=2,11,if(Month(D)=3,12,if(Month(D)=4,1,
if(Month(D)=5,2,if(Month(D)=6,3,if(Month(D)=7,4,if(Month(D)=8,5,
if(Month(D)=9,6,if(Month(D)=10,7,if(Month(D)=11,8,if(Month(D)=12,9))))))))))))as SalesMonthOrder
Resident
Cal;
this what i done for common calendar table .
for individual dates no problem, the problem is with when i click on filters like sales financial year it's showing second table dates only not getting the first table dates.
