A Tool to Compare Variances Across Data Streams
As consultants, we regularly have to deal with unique problems. Every industry, company, and internal unit has their own unique data, needs, and questions. Conversely, similar questions arise regardless of who the client is; How do sales compare versus last year? Are expenses rising? How do our different streams of data compare to one another?
It is that last question that we will discuss in this blog post. A large retail client recently asked us to create a tool in Tableau that would allow users to see how various units in their product hierarchy compared across three different data streams. The tool we published for the client was a major success; for the first time, they were able to quickly see which units had mismatched data, and were able to correct the inaccuracies.
Good for them. How does that help me?
The feedback for that tool was so positive that we converted it into a generic tool that you can download here. By following the steps outlined below, you can tailor our generic data stream comparison tool to your business’ specific needs in just a few minutes.
Getting started with the tool
The client that we worked with had three streams of data: two of which (Financial and Wholesale) should match, and Consumer, which is not expected to match. They were interested in seeing how items in their Category and Sub-category groups compared across the three data streams. In addition, their data included sales ($) and volume (pounds) that can be toggled. On the first dashboard below, there are three views:
- Total Differences by Source. Compares the total value (as dictated by the Value Type parameter to the right) for each unique data stream. You can add or remove as many columns to this matrix view as you wish.
- Financial vs. Wholesale Comparison. A scatter plot which graphically depicts how much deviation each coordinate has from being a matching pair. The dotted line represents a perfect match between financial and wholesale figures.
- Table of differences. The table to the bottom lists, for each member of the selected hierarchy, the total value and the difference between financial and wholesale values.
The second dashboard, Sales vs. Volume Comparison, looks at how two different value types compare for each hierarchy member across each data stream.
Each dashboard contains a number of filters and parameters so you can configure them to your needs.
Tailoring the tool to your needs
We know what you’re saying: “how can I turn this into something that I can use?” There are a few steps required that anybody moderately proficient with Tableau can easily master.
First, and most importantly, you will need to structure your data in two different ways, one in a standard, “wide” format, and the other pivoted into a Tableau-friendly “tall” format. We built a simple Alteryx workflow to pull data from a database and output Tableau extracts. Your data can come from any source; structure it as per the examples below.
The unpivoted data contains one record per year/category/sub-category combination. Each subsequent column represents a different measure value – sales or volume, for example. The pivoted data contains one record per data item. For example, if in the unpivoted data you have 10 year/category/sub-category combinations and five measure columns, the pivoted data should contain fifty records.
Notice how the values in the Name column are the same as the column headers in the first image. This is important, so make sure your data matches this pattern.
Adding and replacing data sources
Once you have your data sources formatted correctly, you will need to add and then replace data sources.
The example workbook that you downloaded contains two data sources – “Cleaned Data – Pivoted” and “Cleaned Data – Unpivoted.” Replace these with your wide- and tall-formatted data, respectively.
Open the Tableau workbook, navigate to the Data menu option, and click the New Data Source button to add each of your data sources. Once they have been added to the workbook, open any of the worksheets, and then navigate back to the Data menu option. This time, select Replace Data Source. For the box labeled “Current,” select either of the original data sources, and for the box labeled “Replacement” you will need to select your corresponding data source. Do this for both sources.
The changes that will need to be made from this point forward are heavily dependent on your data. Unless you renamed your fields to exactly match the sample workbook, you will have to replace references. If you have additional dimensions, measures, etc. then calculated fields and changes to the underlying charts and tables may be necessary.
With a few minor changes, this tool can help you identify inaccuracies across your data. It has been very popular with the client that requested it, and we believe it can help you too.
If you’re interested in learning more, or have questions to get this workbook working for you, don’t hesitate to contact us.