Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a calendar table that has Date and its info like weekNo, Month, Quarter etc..
I have another fact table - Sales that has ID, SalesDate, BookingDate and other info. How do I create association between these 2 tables? Since i have more than one date field in my table its creates circular reference if I try to join.
Basically I need ID, SalesDate, BookingDate,SalesWeek, SalesMonth, Salesmonth, BookingWeek, BookingMonth, Bookingmonth in the Sales table.
Could someone please help?
Perhaps the solution is to simply create the extra fields you need in the Sales table itself from the SalesDate and BookingDate and not use a calendar table at all.
Perhaps the solution is to simply create the extra fields you need in the Sales table itself from the SalesDate and BookingDate and not use a calendar table at all.
Thank you so much, I will see if I can do it
HI Gysbert and Dilip, canonical date works like a charm, thank you very much.
But I have one problem after implementing it.
I'm trying to get the numbers for previous year same date but I get zero, not sure if doing it right.
Here is my expression:
This works:
sum({<DateType={"LeadMarketCreatedDate"}, Date={">=$(vDateFrom) <=$(vDateTo)"}>} LeadFlag)
This doesn't work:
=sum({<DateType={"LeadMarketCreatedDate"}, Date={">=$(vDateFrom_1) <=$(vDateTo_1)"}>} LeadFlag)
vDateFrom_1 is: num((Date(AddYears(min(Date), -1))))
vDateTo_1 is: num((Date(AddYears(max(Date), -1))))
Thanks
Ganesh