2020: Week 8

Hello Preppers, we often get asked what is a nice way to start learning how to use Tableau Prep Builder. This exchange happens a lot in the Tableau Prep community...

So I thought it was time to create another, CLICK ONLY (and changing some column titles) solution so people can explore the functionality in Prep. Remember to show you are taking part by filling in the participation tracker which due to Rosario's idea on keeping track of level of complexity, we should soon have a view on difficulty of each challenge too.

The challenge this week is pulling together multiple weeks worth of data that get added by an additional worksheet being added to an Excel Workbook. But, all the files to reference needed to be added in to context using a different spreadsheet (in the same workbook) from our Financial Planner, Jules. Jules loves a spreadsheet and hasn't gotten used to these "modern data tools things". Jules' data source is a mess - it's your job to turn it in to something useful.


Remember no typed calculations or filters!

Requirements


  • Input Data (*** Updated on 20th Feb 9pm GMT ***)
  • Pull all the Week worksheets together
  • Form weekly sales volume and value figures per product
  • Prepare the data in the Profit table for comparison against the actual volumes and values
  • Join the tables but only bring back those that have exceeded their Profit Min points for both Value and Volume
  • Prepare the Budget Volumes and Values for comparison against the actual volumes and values
  • Join the tables but only return those that haven't reached the budget expected for either Value or Volume
  • Prepare the outputs
  • Output Data

Outputs

Two Files:
1. Week's Records where Budgets have not been reached for Values or Volumes

  • 6 Rows (7 including headers)
  • 6 Columns:
    • Type
    • Week
    • Sales Volume
    • Sales Value
    • Volume (Budget)
    • Value (Budget)
2. Week's Records where Profit Expectations have been exceeded for Volume and Value
  • 6 Rows (7 incl. headers)
  • 6 Columns:
    • Type
    • Week
    • Sales Volume
    • Sales Value
    • Profit Min Sales Volume
    • Profit Min Sales Value

Hints and Tips

  • You will probably need to use:
    • Union
    • Join
    • Pivot - Rows to Columns and Columns to Rows
    • Aggregation Steps
  • Joins can act as filters if you set the conditions correctly!

The outputs can be found here for comparison. Don’t forget to fill the participation tracker and share your solutions using #PreppinData on Twitter.

Comments

Popular posts from this blog

How to use... Custom SQL in Prep Builder

2019: Week 1

How to... Aggregate in Prep Builder