Joining two data sources with similar fields in a chart

[orangebloss]

I have 2 tables as below, I can't create a common Year / Week column as that creates a circular reference.

The plan table contains all possible Year / Week combinations that may appear in the actuals table. I want to plot plan vs actuals on a chart to show where these don't match up as in the excel chart below. What's the best way to do this without causing a circular reference?

Plan vs Actual

Table 1 Plan

Year / Week	Project Code	Type	Total
2024/01	123	Build	1
2024/01	456	Design	5
2024/02	123	Design	3
2024/03	123	Build	0.5
2024/04
2024/05	456	Test	5

Table 2 Actuals

Year / Week	Project Code	Type	Total
2024/01	456	Build	1
2024/01	123	Design	5
2024/03	123	Build	0.5
2024/04
2024/05	456	Design	5

[Clement15]

Hello, wouldn't concatenating the two tables but adding a TAG to differentiate between Plan and Actuals solve your problem? Then, simply use the set analysis by choosing the TAG corresponding to your measure.

[orangebloss]

I would but there are already a lot of joins in the app that I don't want to mess up! The tables do look to be concatenated already as below.

The YYYYWW field in table 1 is the master list of all the YYYYWW that I want to plot the plan and actuals against.

Actuals:
LOAD
    Project,
    Nmhrs,
    OThrs,
    (Nmhrs)+(OThrs) as Actual_Hours,
   ProjectID,
     TdateYear &'/' &num(TdateWeek, '00') as TYearWeek,
Type
FROM [Table2 file]

Concatenate
LOAD
    Project_ID		as ProjectID,
    Dep_ID			as Type
FROM [Table3 file]

Concatenate
LOAD
ProjectNo					as ProjectID,
    "Resource Type"				as Type
   " YYYYWW"						as TYearWeek,
    
FROM [Table1]

[Clement15]

I don't know what you're trying to do but is it normal that you use concatenates rather than joins here?

[marcus_sommer]

I could imagine that you get better results by joining at first table1 to table3 and then concatenating it to table2.

Beside this the fear of mess something indicates that there are already too many load-statements within the application - not mainly to any technically and/or performance aspects else to the efforts of developing/maintaining and keeping a sufficient control and overview. Therefore distributing the job to several applications and/or layer might be helpful.