Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two different dates that are not related two each other. I thought I could do something like:
CommonDates:
Load
WOID,
RequestDate as CommonDate
Resident WorkOrders;
Concatenate(CommonDates)
Load
BillID,
BillDate as CommonDate
Resident UD_BillAmount;
Set vFM = 7 ; // First month of fiscal year
Set vFD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
MasterCalendar:
Load distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil((CommonDate-StartOfFWeekOne+1)/7) as FWeekNo,
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
WeekStart(CommonDate,0,$(vFD)) as StartOfFWeekOne,
*;
Load
CommonDate,
YearStart(CommonDate,0,$(vFM)) as FYearStart,
Year(CommonDate) as Year,
Month(CommonDate) as Month,
Date(Monthstart(CommonDate), 'MMM-YYYY') as MonthYear,
Week(CommonDate) as ISOWeekNo,
Dual(WeekDay(CommonDate),Mod(WeekDay(CommonDate-$(vFD)),7)+1) as WeekDay,
Day(CommonDate) as Day
// Date(BillDate'MM/DD') as DATEMMDD
Resident CommonDates;
Then I can use CommonDate as my dimension, expression, etc. It is not working.
Thanks,
David
Hi David,
Based on your script, you created your MasterCalendar, however your dates in Work Order table and in the Billing table remain detached from the Common Date, and that's most likely the reason why it doesn't work.
The best solution in your case is to create a single fact table that combines both Work Orders and Bills, with a qualifying field that differentiates between the two, and to name both dates with the same field name. Than, your Master Calendar can be linked to that single field and work properly.
You can find multiple references to the process of building a single fact table.
cheers,
Oleg Troyansky
Check out my book QlikView Your Business - available to pre-order on Amazon!
Indeed you can do that.
But what are you trying to accomplish? And what is not working? "It" is a bit thin to investigate...
BTW your calendar is not really a Master Calendar in that it contains the same dates as your facts table (no continuous timeline) and could easily be joined to your facts altogether.
Hi David,
Based on your script, you created your MasterCalendar, however your dates in Work Order table and in the Billing table remain detached from the Common Date, and that's most likely the reason why it doesn't work.
The best solution in your case is to create a single fact table that combines both Work Orders and Bills, with a qualifying field that differentiates between the two, and to name both dates with the same field name. Than, your Master Calendar can be linked to that single field and work properly.
You can find multiple references to the process of building a single fact table.
cheers,
Oleg Troyansky
Check out my book QlikView Your Business - available to pre-order on Amazon!