2020: Week 45 - Solution

Solution by Tom Prowse and you can download the workflow here. 

This week we launched our new business: Prep Air. This is our airline that will fly between some of the world's major data hubs and other holiday destinations.

For the challenge this week we looked at the first quarters revenue and then try to determine what the rest of the year would have looked like if the world hadn't stopped. 

Step 1 - Calculate Sales, Targets, & Variance

The first step this week is to combine the Ticket Sales & Sales Target tables. We can do this by joining the two together with the following join conditions: 

We can then rename our value fields so that they match the correct values for Sales & Target. Then use both of these fields to calculate the Variance to Target: 

[Sales Value]-[Target Value]

Our table should now look like this: 

Step 2 - Clean Projections Table

Next, we want to turn our focus onto the 2020 Projections table and make some changes so that it can be combined with our initial table. First, we want to pivot the data so that we have all of the projections in a single column: 

We now want to turn the Q Change % field into a number. Therefore we need to replace the 'Plus' & 'Minus' so that they can be turned into a positive or negative number. To do this we use the following calculation:

Q Change

IF CONTAINS([Q change %],'Plus') THEN 

REPLACE([Q change %], 'Plus','')

ELSE

REPLACE([Q change %],'Minus ','-')

END

We can then change the data type to a Whole Number, and we will have our field within a number format. 

Next, we want to parse which quarter each of the projections belong to. We can extract the number by using an automatic split on the Pivot1 Names field, then using a string calculation to add the letter 'Q' before each of the numbers: 

Quarter
'Q'+[Quarter]

Our projections table should now look like this:

Step 3 - Combine Airports

The next step is to combine the airport information into our Sales table. We need to find out the country for both the Origin & Destination airports, and we can achieve this by using a couple of joins. 

The first join is going to be for the destination airport: 

The second join is for the Origin airport:

Our workflow now looks like this and both our origin and destination airports should now have a country and airport name associated with them: 

Step 4 - Calculation Projections

We are now ready to calculate the projected values based on the Q1 results. First, we need to calculate the total sales, target, & variance for Q1 for each of the different routes. To do this we use an aggregate tool with the following setup: 

After we have aggregated so that we have a total for each route, we can now join this with our projections table. We want to join each of our projections (Q2-4) onto each of our Q1 rows based on the destination country:

After combining the tables, we can start to calculate the projections for future quarters. First, we want to change our projections into a % using this calculation: 

% Change

1+([Q Change]/100)

 We can then apply this calculation to each of our values: 

Sales Value
[Sales Value]*[% Change]

Target Value
[Target Value]* [% Change]

Variance to Target
[Variance to Target]*[% Change]

Now we have projections for each of the future quarters, we need to combine with our values from Q1 by using a union to stack the values on top of each other. Our data now looks like this: 

Step 5 - Calculate Yearly Totals

The final step this week is to calculate the yearly totals for each of our metrics. To do this we can use an aggregation tool with the following setup: 

Using the aggregation will also remove any of the additional fields that we no longer need, so that our data is now ready for the output: 

The full output 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, @JonathanAllenby & @TomProwse1

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!