Multiple facts different dates but need to select data

[l_smythe80]

Hi There,

hopefully someone can help with this extremely complex question (it may well not be able to be answered).

i currently have 2 fact tables which both contain different date fields however there are some keys that are common in both fact tables (i will try to draw something to explain this) however not all key fields will be in both fact tables.

what i want to do is in theory combine the 2 fact tables and if i select a date from fact 1 i want to keep the association to fact 2.

fOr example

i select '01/01/2019' from date field 1 i want to show all corresponding values in fact 1 & fact 2 that link based on the key1 field. so in the example shown i select key1='ABC123' and date1='01/01/2019' which sums the value £100 from field 1, but i also want to show the sum of the date1 = '01/02/2019&01/03/2019' and date3='01/07/2019' because these have the same key1 but a different date. so there is still assocation between the key1 field but different by date. so the sum of field 1 = £300 and sum of field 2 = £100 even though i have selected 01/01/2019

as i say i dont know if this is possible or if i need to go down the link table route but based on many dimesion table and 2 fact tables and many calendars this seems like the data model could become very slow with c200million rows in there. i am just looking to keep the association even though im selecting a date

any help would be great

[Arthur_Fong]

Hi,

Do you mean something like this?

Thanks and regards,

Arthur Fong

[Arthur_Fong]

Qvw is attached as reference.

Thanks.

[l_smythe80]

yes kind of, thats great in a chart/table, but im hoping more to keep the association across the whole app, so KPIs also saying £300 and £100 not just in the chart.

i am thinking there isnt anything easy and will require a link table, but the model will be huge and lot going through the link table. plus there are multiple calendars coming off each fact.

[Brett_Bleess]

I may be way off here, but I wanted to toss it out, it's a Design Blog post, hopefully it might be of some use here:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Regards,
Brett