topic Re: Island table in App Development

Island table

GaneshLakshman — Fri, 26 Apr 2019 13:17:00 GMT

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?

Re: Island table

Gysbert_Wassenaar — Fri, 26 Apr 2019 13:35:00 GMT

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.

Re: Island table

dplr-rn — Fri, 26 Apr 2019 13:53:50 GMT

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

Re: Island table

GaneshLakshman — Sat, 27 Apr 2019 06:11:50 GMT

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

Re: Island table

GaneshLakshman — Sat, 27 Apr 2019 15:10:27 GMT

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