
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Joining two data sources with similar fields in a chart
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 |
- Tags:
- chart
- line graph

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't know what you're trying to do but is it normal that you use concatenates rather than joins here?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
