2022: Week 23 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week we have another collaboration and this time it's with our old friends at #WorkoutWednesday. For this collaboration we are looking to connect to Salesforce data and look to analyse some opportunity data.
Step 1 - Opportunity Table
The first step this week is to input the Opportunity table and then reshape it so that we have a row for when each Opportunity was Opened and a row for the Expected Close Data.
We can use a Columns to Rows pivot for this, where we pivot on the CloseDate & CreatedDate fields:
After the pivot we can rename the auto generated pivot fields:
- Pivot1 Values to Date
- Pivot1 Names to Stage
Then within the Stage field, we can rename the values:
- CreatedDate to Opened
- CloseDate to ExpectedCloseDate
We also need to rename a couple of the other fields within the Stage field:
Stage
IF [Stage]='ExpectedCloseDate' AND CONTAINS([StageName],'Close')
THEN [StageName]
ELSE [Stage]
END
Then finally we can remove a lot of the fields that we don't need to work with. From the table we want to Keep Only the Date, Stage, & ID fields.
Step 2 - Opportunity History Table
We can now turn to combining the Opportunity History table as well. As this is in a similar structure as the Opportunity table, we can 'stack' these on top of each other using the Union tool. At this stage we can remove the Table Names & SortOrder fields (we will bring the SortOrder back in later) so our table looks like this:
To bring the SortOrder back in so that each row one associated with it, we can use the Opportunity History table as a lookup. First we need to use an aggregation tool to Group the Stage Name & SortOrder fields so that we have a row for each combination:
Then we can join this lookup table to our existing workflow to associate each row with the SortOrder. To combine the tables we need to use a Left (or Right depending on your setup) join to ensure that we are retaining all of the rows from the existing workflow. We want to join where Stage = StageName:
Finally update a couple of the SortOrder values to correctly match the requirements using this calculation:
SortOrder
IF [Stage]='Opened'
THEN 0
ELSEIF [Stage]='ExpectedCloseDate'
THEN 11
ELSE [SortOrder]
END
The final step is to remove any values that have been duplicated when bringing the tables together. We can use an aggregation tool to Group all of our fields so we are left with a single row for each combination:
After the aggregation we are ready to output our data:
The full output can be downloaded here.
Once you have completed the challenge head over to the Workout Wednesday challenges (Tableau or PowerBI) so that you can use this data set to create & visualise your Salesforce Opportunities Dashboard!
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and #WOW2022 and tagging @Datajedininja, @JennyMartinDS14, @TomProwse1, @_Lorna_Brown, @LukeStanke, @HipsterVizNinja, @_hughej, and @YetterDataViz
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!
