Set Analysis based on another date in another table

[RsQK]

Hi, I've come across a new one for me. In a simplified scenario I have three tables:

Table1:
LOAD *,
NUM(MONTHSTART(Date)) AS Period.#key,
1 AS Amount;

LOAD * INLINE [
ID.#key,Date,User
1,01.01.2020,John
2,05.01.2020,Kenny
3,17.01.2020,Yuri
4,02.02.2020,Max
5,03.02.2020,John
6,04.02.2020,Kenny
7,06.02.2020,Michael
8,09.02.2020,Sandy
9,10.02.2020,Mike
10,17.02.2020,Kate
11,01.03.2020,Hope
12,05.03.2020,Chris
13,13.03.2020,Laura
14,15.03.2020,Lewis
15,19.03.2020,Otto
];

Table2:
LOAD *,
DATE(MONTHSTART(Date),'MM-YYYY') AS Period,
1 as Amount;

LOAD * INLINE [
ID.#key,Date,Type
1,04.01.2020,T
2,01.02.2020,R
3,29.01.2020,T
4,01.03.2020,R
5,07.02.2020,Z
6,05.02.2020,R
7,10.02.2020,T
8,14.02.2020,Z
9,01.03.2020,T
10,01.03.2020,Z
11,04.03.2020,R
12,15.03.2020,R
13,17.03.2020,T
14,24.03.2020,Z
15,30.03.2020,Z
];

Calendar:
LOAD DISTINCT
[Period.#key],
YEAR([Period.#key]) AS Year,
MONTH([Period.#key]) AS Month,
DATE([Period.#key],'MM-YYYY') AS Period
RESIDENT Table1;

And this is where I got stuck. I need to create a table that contains the period from calendar and two measures. One of them is Sum of Amount from Table 1, which isn't a problem at all. But I also need the Sum of Amount from Table 2, where period from Table2 matches the period of calendar (for example, in January of 2020, I'd expect to get 3 as the first measure and 2 as the second measure). I've tackled with P(), done all sorts of things, but just can't get it to work.

Thanks in advance.

[TimvB]

Table1 and Table2 are Fact tables (that include measure data), so you should concatenate the two tables and give the Period field the same field name as in the MasterCalendar table. This way, all fact data is linked to the MasterCalendar data.

Hope it helps!

[RsQK]

Thanks for the response, but concatenation isn't an option in this case.

[TimvB]

Maybe linking the fact tables as described here: http://www.learnallbi.com/link-table-in-qlikview/

[petter]

You could simply use this approach:

Table1:
LOAD *,
NUM(MonthStart(Date1)) AS Period.#key,
1 AS Amount1;
​
LOAD * INLINE [
ID.#key1,Date1,User
1,01.01.2020,John
2,05.01.2020,Kenny
3,17.01.2020,Yuri
4,02.02.2020,Max
5,03.02.2020,John
6,04.02.2020,Kenny
7,06.02.2020,Michael
8,09.02.2020,Sandy
9,10.02.2020,Mike
10,17.02.2020,Kate
11,01.03.2020,Hope
12,05.03.2020,Chris
13,13.03.2020,Laura
14,15.03.2020,Lewis
15,19.03.2020,Otto
];
​
Table2:
LOAD *,
NUM(MONTHSTART(Date2)) AS Period.#key,
1 as Amount2;
​
LOAD * INLINE [
ID.#key2,Date2,Type
1,04.01.2020,T
2,01.02.2020,R
3,29.01.2020,T
4,01.03.2020,R
5,07.02.2020,Z
6,05.02.2020,R
7,10.02.2020,T
8,14.02.2020,Z
9,01.03.2020,T
10,01.03.2020,Z
11,04.03.2020,R
12,15.03.2020,R
13,17.03.2020,T
14,24.03.2020,Z
15,30.03.2020,Z
];
​
Calendar:
LOAD
[Period.#key],
YEAR([Period.#key]) AS Year,
MONTH([Period.#key]) AS Month,
DATE([Period.#key],'MM-YYYY') AS Period
RESIDENT Table1;
​

Then you wouldn't need to use Set Analysis at all - you just take advantage of the association between the three tables by period:

[petter]

Linking the three tables on Period.#key will do the trick I believe (according to your stated requirements...), and you wouldn't need to use a Set Expression (aka. Set Analysis):

Table1:
LOAD *,
NUM(MonthStart(Date1)) AS Period.#key,
1 AS Amount1;

LOAD * INLINE [
ID.#key1,Date1,User
1,01.01.2020,John
2,05.01.2020,Kenny
3,17.01.2020,Yuri
4,02.02.2020,Max
5,03.02.2020,John
6,04.02.2020,Kenny
7,06.02.2020,Michael
8,09.02.2020,Sandy
9,10.02.2020,Mike
10,17.02.2020,Kate
11,01.03.2020,Hope
12,05.03.2020,Chris
13,13.03.2020,Laura
14,15.03.2020,Lewis
15,19.03.2020,Otto
];

Table2:
LOAD *,
NUM(MONTHSTART(Date2)) AS Period.#key,
1 as Amount2;

LOAD * INLINE [
ID.#key2,Date2,Type
1,04.01.2020,T
2,01.02.2020,R
3,29.01.2020,T
4,01.03.2020,R
5,07.02.2020,Z
6,05.02.2020,R
7,10.02.2020,T
8,14.02.2020,Z
9,01.03.2020,T
10,01.03.2020,Z
11,04.03.2020,R
12,15.03.2020,R
13,17.03.2020,T
14,24.03.2020,Z
15,30.03.2020,Z
];

Calendar:
LOAD
[Period.#key],
YEAR([Period.#key]) AS Year,
MONTH([Period.#key]) AS Month,
DATE([Period.#key],'MM-YYYY') AS Period
RESIDENT Table1;

[RsQK]

Thanks for the response!

Actually T1 and T2 are linked through the ID.#key, so linking them just by the dates wouldn't suffice.

[petter · Accepted solution]

Then you could simply keep the ID.#key common between Table1 and Table2. And you make the Period common between Table1 and Calendar. 

Whether you create a data model that connects the tables or do set expressions on-the-fly to connect the right values in various fields and tables or a combination and balance between the two depends on you total application and analysis need.

[petter]

Please click the like button or heck if the response was correct you could even mark it as a correct solution.
Believe me - it will be greatly appriciated by the contributors ...
We love likes as much as anyone posting anything on social media