2022: Week 38 - Solution
Solution by Tom Prowse and you can download the workflow here.
The challenge this week focusses on recreating the standard Salesforce connections that are available in Tableau Desktop. For this challenge, you can create your own Salesforce account and use the Salesforce connector, or alternatively use the text files provided. For this solution I have used the text files but they will be the same if you are connecting via Salesforce.
Step 1 - Remove Fields
The first step that we need to do this week is input our 3 tables, and then select the fields that we need. We can remove a lot of the unneeded fields at the input step stage, therefore we need the following from each table:
Opportunity
- OpportunityID
- OpportunityID
- AccountID
- Opportunity Name
- Stage Name
- Amount
- OwnerID
- CreatedByID
User
- ID
- Name
Account
- AccountID
- Account Name
- Account Type
- OwnerID (Rename to AccountOwnerID)
- CreatedByID (Rename to AccountCreatedByID)
Step 2 - Opportunity & Users
For the first section we want to focus on the Opportunity and User tables. We want to recreate the joins so that we can associate a name with the Created By ID and Owner ID fields.
To do this we need two joins:
Created By ID
Inner join where CreatedByID = ID
Owner ID
The second join that we need is an inner join where OwnerID = ID
This is Output 2:
Again, we want to rename the Name field to Owner Name and also remove the ID field.
Our table should now look like this:
Step 3 - Opportunities & Accounts
Next we want to start including the Accounts table into our workflow. We again need another join, but this time we are joining for our workflow with the Account table where AccountID = AccountID
We then want to include the Account Created By name, therefore we want to join our existing workflow back to the User table. This time it's an inner join where Account Created By ID = ID
The final join involves finding the Account Owner Name, therefore we again need to join back to the User table. This is an inner join where Account Owner ID = ID
We can then remove any additional ID fields and rename the correct Name fields so our table looks like this:
This is our first output and the standard opportunities connection in Tableau Desktop.
Step 4 - Additional Outputs
Now we have the standard connection we can start to create the aggregated tables for the other outputs. Each of the aggregates will appear on a separate branch.
Who is the Opportunity Owner with the Highest Amount?
Here we need to aggregate to group by Owner Name then Sum the Amount field.
Who is the Account Owner with the Highest Amount?
Again we need to aggregate, but this time group by Account Owner Name and again sum the Amount:
Here's Output 3:
Which Account has the most Opportunities & Amount?
The final output we need to aggregate by grouping by Account Name, then sum the amount and count distinct on Opportunity ID.
Here's Output 4:
The full outputs can be downloaded here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14, @TomProwse1 and @_Lorna_Brown
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
