Common date filtering

[NormanStanleyBadger]

Hello Everyone 🙂

Ok, so I need some help on common dates.

I have a dashboard with measures from effectively three different "datamarts", transport, inspections & sales.

My plan was to use the CommonDate filters to allow users to filter and then capture the min and max common dates and feed them back into the measures for the relevant date fields in the respective transaction tables, while blocking overall common date filtering with the below. 

[CommonDate.autoCalendar.Fiscal Year]=,

However, it is not working and records that are in scope are being lost when the filter is selected.

What am I missing? 

Thanks in advance.

[Oleg_Troyansky]

Hi Norman,

The answer largely depends on your particular data model and on the way your data elements are associated with each other.

Ideally, I'd try to bring the three fact tables into a single Concatenated Fact data model, and name the dates from these 3 facts with the same field name. This is possible if each fact is characterized by one single date. It might be more difficult if you have several different date fields in each fact.

Also, you can try placing the "common date" field in a detached (island) calendar. This is a bit dangerous, because if it's used incorrectly, it could cause a Cartesian join between the island and the facts. The right way of doing that is using Set Analysis function P(), like this:

{<ShipDate = P(CommonDate)>}

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[NormanStanleyBadger]

Crikey, not every day you get your question answered by a legend ! Your site is awesome and is absolute treasure trove. Thank you. Unfortunately, I am not the owner of this data model, but I will feed this back, for sure. 

[Oleg_Troyansky]

Thank you for your kind words, it means a lot!