Data Visualization - Insight From Your Data

Passing A Date Parameter

I recently had an interesting challenge that required the passing of two date parameters from an Excel spreadsheet over to an Access database in order to return data that met the parameters of data between the first date and the second date. The Excel spreadsheet template reported the data in a variety of static reports based on the date range of the data returned from the Access database. The solution to this problem was to use the Microsoft Query software available in the Excel spreadsheet. The following is a demonstration of how you can pull data from a Access database back into an Excel spreadsheet.

The following is an example of the report we want to populate from the Access database with data that meets a certain date range:

Our Access database contains over four years of data at the weekly level, but we will only want to pull back certain weeks of data to populate our report. The first step in our process will be to set up an external data pull using Microsoft Query:

We simply need to follow the prompts to find the appropriate Access database:

The next step is to select the table we want to extract our data from and select the fields from that table that we want to display in our data extract:

Then we follow the prompts through the Microsoft Query Wizard until we get to the point where the Wizard asked us where we want to return the data to and select "View data or edit query in Microsoft Query":

In Microsoft Query click on Show/Hide Criteria to make visible the criteria fields and then select from the criteria field the desired field that we will want to pass a parameter to:

In our example query we have selected the week ending field for which to develop our parameters to be passed and we will enter the Access formula for Between to enter the start date and the end date for our query to select data from the associated Access database table:

Please note that we used the opening and closing square brackets to indicate that the query will expect a parameter that we are naming Start and a second parameter that we are naming End as our starting and ending date parameters. When we have completed our parameter formula simply click on the File menu at the top and then click on the bottom option "Return data to Microsoft Excel" and we will be presented with the only option available which is to return the data as a Table. Select OK and the table will be populated in the Excel spreadsheet.

Now we are ready to set up our date parameter fields to pass to the access database, and we do this by navigating to the data tab and clicking on the Connections menu item. Select the two feels that we want to identify as our Start date and our End date at a convenient and strategic spot in the Excel spreadsheet; in our example we will go to Sheet2 and use cell A1 for our Start date and cell A2 for our End date. In our example we want to pull data from the Access database between the dates of October 9, 2010 (10/9/2010) and April 23, 2011 (4/23/2011).

The final step in the process is to now tell Microsoft Query which of these fields is related to the start date and which is related to the end date. We do this by navigating to the Data tab and then select Connection menu option:

From the Connections wizard click on the properties button on the right side of the wizard, select the Definitions tab and click on the Parameters button at the bottom of the dialog box.

 

In the parameters dialog box first select the Start date parameter and identify the cell associated with it (in our example cell A1 on Sheet2) then select the End date parameter (in our example cell A2 on Sheet2).

Click on the OK box and the processes now completed. There are a couple of caveats with this approach; for example you can only deliver the data back into a table and not a pivot table, however there are various formulas which we will cover in subsequent newsletters that will allow you to aggregate the data using SUMIFS and COUNTIFS formulas.

Insight, Information & Consulting Services is a data visualization consulting firm that can help your company set up Excel templates that connect to Access databases, SQL databases as well as other data sources to provide your field sales and marketing teams with actionable data delivery tools. Please contact Rick Pensa at 770-425-4243 for further information.

Conditional Average

Have you ever found yourself needing to average 1,000 lines of data on a spreadsheet with a ton of zeros across a number of ranges such as price gap ranges? Maybe you are trying to figure out volume responses based on certain price thresholds or maybe the average lift at various promotion prices.

You can do this with some new functions in Excel 2007 or by simply creating a custom function in earlier versions of Excel (I will provide the syntax later in this article). Suppose you start out with 1,000 lines of data in a spreadsheet that looks like the example in Exhibit 1:

The left column is the price gap between your brand and the Private Label and the right column is the volume you achieved at that gap for that week. We want to know on average what the volume expectations might be at various gap levels i.e. 10¢, 20¢, 30¢ etc.

You can't do a simple average but there is a way to accomplish this calculation using Excel 2007's Sumifs and Countifs. If we want to know the average sales for all instances when the price gap is between 10¢ and 20¢ the formula will look like this:

=Sumifs(Volume, Pr Gap, ">=.10",Pr Gap,"<.20")/Countifs(Pr Gap, ">=.10",Pr Gap,"<.20")
=Sumifs($d$6:$d$1006,$c$6:$c$1006,">=.10",$c$6:$c$1006,"<.20")/Contifs($c$6:$c$1006,">=.10",$c$6:$c$1006,"<.20")

Exhibit 1
This formula could be copied and you would simply adjust the bottom and top ranges of the price gap for each gap threshold i.e. 20¢ to 30¢, 30¢ to 40¢ etc.

If you are still in Excel 2003 or earlier versions you might want to try the following custom function syntax (CountBetween and SumBetween) entered in the Excel Visual Basic editor; simply cut and paste the following sentences into your Visual Basic editor:

Function COUNTBETWEEN(rng, num1, num2)
COUNTBETWEEN = Application.CountIf(rng, "<=" & num2) - Application.CountIf(rng, "<" & num1)
End Function

Function SUMBETWEEN(rng, num1, num2, rng2)
SUMBETWEEN = Application.SumIf(rng, "<=" & num2, rng2) - Application.SumIf(rng, "<" & num1, rng2)
End Function

Then you can enter the following formula in any cell in the Excel worksheet:

=sumbetween($AS$6:$AS$109,.10,.20,$AT$6:$AT$109)/countbetween($AS$6:$AS$109,.10,.20)

The resulting matrix will provide you with an understanding of potential volume responses to a range of price gaps:

Insight, Information & Consulting Services, Inc. is a consulting company dedicated to providing data visualization tools to clients in the Consumer Goods industry. Please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.

Pivot Tables To The Rescue-Part II: Custom Market Shares

Did you know you can produce custom market shares in an Excel pivot table which reflect how you identify the category segment? Last month, we learned what a terrific tool Excel can be for data analysis, by building a pivot table to display a pie chart for brand share and a bar chart to depict percent change vs. year ago. This month, I want to expand upon the data manipulation capabilities of a pivot table, by showing how we can produce a custom market share. It is often difficult to create dynamic brand shares based on segmenting a category. For example if we want to show Private Label's share of Low Fat Milk for Large Sizes (half gallons & gallon sizes) and then quickly see Private Label's share of single serve milk. With a pivot table we can set the size and fat level characteristics to create the dynamic segment definition but how can we create a dynamic brand share calculation to work just as quickly?

The following examples shows you how to build a dynamic share calculation in a pivot table:

Step1: Start your pivot table with a list style "flat file" as the following example illustrates.

Step 2: In the upper left hand corner (cell A1) of the data set, click on the Insert tab in Excel 2007 (pictured below) and in Excel 2003 (Data/Pivot Tables) to produce the pivot table (not shown). Excel will automatically select the data range for you. Select "New Worksheet" and "OK" from the radio buttons to get to the section where you arrange the data in the pivot table.

Now, build the pivot table using the market and some characteristics from your data. This segmentation is determined by how your company views the category. For example, the dairy industry might consider the milk category to be segmented by fat content (Whole Milk vs. 2% Milk vs. Skim Milk), size (Gallons, Half Gallons, Quarts, Single Sale), and then by brand. In this exercise, our objective is to report the brand share for each segment as we drill down through the category definition.

Step 3: Select "Market" and "Fat Content," and "Size Category" and "Period" by dragging them into the Report Filter box on the right side. Drag "Brand" into the Row Labels box and "Dollars" into the Values box as illustrated below.

Step 4: In Column B1, select Charlotte from the drop down menu. In B2, select Fat Free from the drop down menu. In B3, select Large from the drop down menu, and in cell B4, select Period-12 weeks ending June 2009 from the drop down menu.

We want to know the brand dollar share of the Fat Free (Skim) Milk segment of the Large Size (Half Gallons & Gallons), in the Charlotte market. Excel allows us to use the Dollar Sales measure again in the Values box and make it a Dollar Share measure rather than a Dollar Sales measure.

Step 5: Drag a second "Dollars" into the Values box (lower right).

Click on the second "Dollars" drop down menu and select "Value Field Settings." Select the "Show Value As" tab and change the "Normal" setting to "% of Column," as in the following screenshots. Excel automatically converts the dollars to percent of total. (By clicking on the second "Dollars" listed, you will get dollars on the left and percentage share on the right. You can also use the "Value Field Settings" screen to change the number format to reflect currency (not shown) by clicking on the "Number Format" button. You can also change the column headings in the Pivot Table on the "Value Field Settings" screen (not shown) by typing in the Custom Name box.)

Excel displays the share value instantly for the brands that make up whatever segment we have defined.

In our example, you can easily see that Private Label dominates Large Sized, Fat Free Milk with an 83% share of Fat Free/Large Milk. Using the drop down menus in Column B to select Single Serve and All Fat Contents, you can view your data in a new way.

Now you see that Private Label is dominant, but not as dominant in the Single Serve/All Fat Contents segment with a 39% share of category. You can see what a powerful tool Excel Pivot Tables can be to analyze your company's information. You can look at your data from many perspectives with the click of a button.

Insight, Information & Consulting Services, Inc. can help your company develop a powerful data visualization strategy using simple readily available tools such as Excel or we can help your company deliver data in a powerful thin client tool designed to build and update thousands of charts with the click of a button. Visit our website at www.insightinformation.net or call us at 770-425-4243.

Pivot Tables To The Rescue

Did you know that you can develop a Microsoft Excel template that you can distribute to your extended users such as the field sales force? You can leverage Pivot Tables within Microsoft Excel to develop impactful tools to drive actionable insights to the ultimate end users in your sales and marketing organizations.

Let me show you how to build an Excel template to distribute syndicated data to your sales team who don't have access to the data. Since most people have Excel on their computers this provides an easy way to get specific issue driven templates and information into the hands of the end users of the data.

The first step is to organize your data in a flat file. A flat file is defined as an unformatted square "chunk" of data as the following example will show:

We can now go to cell A1, from the insert menu select the pivot table option and chose the entire set of data to build a Pivot Table (I am using Excel 2007 to build our example). We can choose to create a new worksheet or we can decide to place the pivot table on the current worksheet. If you select ok, it will automatically define the range it interprets and default to a new sheet within the same Excel file:

We can drag fields from the pivot table field list to the various elements of the pivot table report to build our report layout:

1. Drag Markets (MKT) and Periods (PER) in the Report Filter section of the report so we can select one or more markets to view and a time frame (4 Weeks, 12 Weeks or 52 Weeks).
   
2. Drag the Products (PROD) field to the row labels box and since we want to see dollar shares and the impact of trended dollar sales.
   
3. Drag Dollar Volume ($ VOL) and Dollar Volume Change versus year ago (DOLL VOL CHG VS YAG) to the values box.
   
4. The Pivot Table will be automatically created.

Our report output needs to be presentation ready so we will want to have two separate graphs populated when we select a market. For example I want a pie chart to show the shares of the brands in a selected market and the bar chart to show the dollar trend changes for each brand versus the same period one year ago. To do this we can build data ranges outside of the pivot table to be charted in our template. First, let's set up the table for our pie chart:

1. Move to a separate place on your worksheet, beneath or beside your Pivot Table.
   
2. Use the "=" function to reference the data from the pivot table and fill the table you are creating.
   1. Place an "=" in the new "pie chart" table, in the product name cell (A15 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
   2. Repeat until all products are represented.

Next, we will reference the dollar sales fields in the same manner

1. Place an "=" in the dollar sales value cell (B15) and then clicking on cell B6.
2. Repeat until all of the product's dollar sales are represented.

Now, let's create the table to be used for the bar chart.

1. Reference the percent change in dollar sales like we did in the pie chart, starting with the product name.
   1. Place an "=" in the new "bar chart" table, in the product name cell (A23 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.
   2. Repeat until all products are represented.
2. Now, reference the percent change in dollar sales.

We will then produce each of two graphs (a Pie Chart and a Bar Chart) on one page of our template in the following manner:

• Highlight the area to be graphed
• Select the insert menu and pick the pie chart option
• Format a pie chart as you normally would
• Create a second chart in the same manner selecting the bar chart option

Now, for with a little cleanup effort such as:

• Allowing hidden data to display in the graph
  • Right mouse on the graph object and pick "Select Data" for the menu
  • Click on "Hidden and Empty Cells" (bottom left in the dialogue box)
  • Click "On" the "Show Data In Hidden Row & Columns"
• Align graphs under the pivot table drop downs
  
• Hide the rows that show the data set for the pie chart and bar chart
  
• Turn off gridlines and headings on the Page Layout Ribbon (Excel 207)

Your template is now completed and you can see market shares and volume trends in one easy to use template that can be printed off and presented to your customers:

The above template is a very simple example of creating an Excel tool to better visualize your data as real and actionable information. Additional levels of sophistication can be added such as intelligent text bullet points, dynamic rank reports, navigation buttons to navigate the user between multiple charts and tabular data visualization charts. In future editions of the Insight newsletter we will demonstrate how to build these enhancements into the template.

Insight, Information & Consulting Services, Inc. is dedicated to delivering better data visualization tools to its clients. We develop data visualization tools for Trade Promotion Analysis, Category Management, Consumer Analysis and sales & marketing business analysis templates. Please contact us at 770-425-4243 or visit our website at www.insightinformation.net.