You will probably want to look at blending the 3 sources - The primary would be you Master Classification and the other 2 would link to it on the project number
at the link below you will find a recorded Blending Traing video - it is very good and covers the subject
Let me know if this helped
2 of 2 people found this helpful
If all three tables are in the same Access database, you shouldn't have any issues joining them together in Tableau:
I would recommend Master Projects as the first table and then join Actual and Budgeted to that using Left joins on the Project field. That way, you'll have all the projects for your analysis.
The names of the fields are a bit cleaned up here (you can rename using the drop down arrow next to the field name in the preview).
After you've created the connection, a few calculations will help:
1. You'll have a few rows that you don't want: where the periods don't match.
Create a field named Mismatched Periods with the code:
IF NOT ISNULL([Actual Period]) AND NOT ISNULL([Budget Period])
AND [Actual Period] <> [Budget Period]
Use that as a data source filter and exclude the Mismatch rows
Another calculation will help you identify whether projects have been budgeted, eventuated or both:
IF ISNULL([Project (Actual)]) AND NOT ISNULL([Project (Budgeted)]) THEN "Budgeted Only"
ELSEIF NOT ISNULL([Project (Actual)]) AND ISNULL([Project (Budgeted)]) THEN "Not Budgeted"
ELSEIF ISNULL([Project (Actual)]) AND ISNULL([Project (Budgeted)]) THEN "Not Budgeted or Eventuated"
ELSE "Budgeted and Eventuated"
You can use that as a dimension to filter to the statuses you want to analyze.
To merge the Actual and Budget Periods into a single field you can easily use, create:
IFNULL([Actual Period ], [Budget Period])
And finally, a calculation to give you the difference between budget and actual:
ZN(Budget) - ZN(Actual)
Now, you've got a data source you can work with!
Hope that helps!
Joshua, Shouldn't the last two tables be joined to each other based on BOTH Project number AND Period to keep one to one?
I think that would lose ones that had been budgeted but not eventuated or vice-versa since there is no guarantee that the projects are in both tables. So even a left join wouldn't ensure you ended up with everything. Or is there something I'm not seeing?
Just to elaborate, if you joined like this:
on Budgeted and Actual matching on Project and Period, then you'd still have all the projects, but you'd lose the Actual values for Project C because there were no Budgeted records. So what should be:
ends up being:
And this would work the other way around if you put Actual first and then Budgeted.
But, I'm definitely open to finding out that I've missed something!
Unfortunately this didn't work for me. The blend seemed to be duplicating some of my data!
This worked perfectly! Thank you so much for taking the time to replicate the table and providing a detailed solution. I also liked the 'add-on' suggestion that you made about the 'Project Status' field, I've incorporated this into the visualisation too!
PS I looked in your book 'Learning Tableau' to see if there were any clues in there prior to writing on this forum... Obviously I didn't join the dots, it's there under 'Fixing Data Issues'! Just not spelled out to me like you did in your response.
You're very welcome! I'm very glad the solution worked! And thank you also for sharing about your experience with the Learning Tableau - I'm excited to hear when others have been referencing the book and finding it helpful.