Island table

[GaneshLakshman]

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?

[Gysbert_Wassenaar · Accepted solution]

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.

[dplr-rn · Accepted solution]

Adding to to Gysbert

if you have more than 1 calendar field (sales date, booking date) it is very flxible to use canonical date technique.
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[GaneshLakshman]

Thank you so much, I will see if I can do it

[GaneshLakshman]

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