Date relationships with multiple tables

[yuhkao]

Hi Expert

I have two table need to calculate some meausre with relationship linkage. i already created date table with multiple format of date

dont know how to manage relati

Table 1 - every single event info on each row

Event Date	Product	Classified
5/1/2020	P1	c1
4/3/2020	P2	c2
3/20/2020	P1	c3

...

Table2 - volume with monthly basis

YearMonth	Volume	Products
202001	3000	P1
202001	1000	P2
202001	500	P3
202002	2000	P1
202002	1200	P2
202002	1500	P3

i would like to calculated and make a chart of count of classified on each products with month of year period like below.

	P1	P1	P1	P2	P2	P2	P3	P3	P3
202001	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3
sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3
202003	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3
202004	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3	sum of c1	sum of c2	sum of c3

[amitchandak]

@yuhkao , your output is not very clear

In second table you can have date

Date = date(left([YearMonth],2),right([YearMonth],2),1)

And now you can join both tables with date and common product dimension and create visual based on that

[yuhkao]

@amitchandak yes my problem is how to make correct relationship with 2 tables to make result like table 3 for visual chart. 

[Pragati11]

Hi @yuhkao ,

Just thinking if you need a DATE table or not.

You can create a bridge table containing unique values for - P1, P2, P3, etc.

Use this bridge table to create relationships between you 2 other tables.

Then you can directly move the required fileds to a table or matrix visual to achive the desired result.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Pragati11]

Hi @yuhkao ,

Just wanted to check if you can provide a screenshot around what relationships you have created with these 2 tables and Date table. 

Also what does this "SUM of C1" represent in your data.

please provide details on your query.

Thanks,

Pragati

[yuhkao]

sorry for mistake 

table 3 should be like this.

	202001	202002	202003	202004	202005
P1	c1/volume	c1/volume	c1/volume	c1/volume	c1/volume
	c2/volume	c2/volume	c2/volume	c2/volume	c2/volume
	c3/volume	c3/volume	c3/volume	c3/volume	c3/volume
P2	c1/volume	c1/volume	c1/volume	c1/volume	c1/volume
	c2/volume	c2/volume	c2/volume	c2/volume	c2/volume
	c3/volume	c3/volume	c3/volume	c3/volume	c3/volume
P3	c1/volume	c1/volume	c1/volume	c1/volume	c1/volume
	c2/volume	c2/volume	c2/volume	c2/volume	c2/volume
	c3/volume	c3/volume	c3/volume	c3/volume	c3/volume