Sales Analysis

Slice & Dice the data set ..

PreviousAnalyzer Reports NextEMEA Quantity

Last updated 2 months ago

Was this helpful?

Sales Analysis

Slice & Dice the data set ..

Workshop - Sales Analysis

After completing this workshop, you will be able to:

• Describe the types of fields available in Analyzer

• Add data columns and rows

• Filter data and drill to supporting details

• Add subtotals, grand totals, and calculated fields

• Conditionally format and modify the format of numeric data

• View an analysis in chart and multi-chart format

• Export an analysis to PDF, Excel, or CSV

• Create a Geo Map Interactive Visualization

Leading Product Lines

The analysis shows the Sales, Quantity, and Unit Sales for each year by Territory and Line, and includes subtotal lines for each Territory, as well as a grand total line. The Quantity column includes data bars to quickly visualize and compare values.

The background colour for the Unit Sales column is formatted using a colour scale range. The report values include hyperlinks allowing you to drill down to the supporting data for each value.

To open the Leading Product Lines Report, in the Folders pane, expand Public > Steel Wheels, and then in the Files pane, double-click Leading Product Lines (pivot table).

The Interactive Toolbar includes toggle buttons to add more fields to the report, rearrange fields on the report, and show the filters in use. There are also buttons to undo or redo changes.

The More actions and options button provides export options, additional report and chart options, and reset options.

To view the available fields, on the interactive toolbar, click the Add more fields onto the report button.

The Available Fields pane shows the data source and the fields available. The fields are listed by category, but you can use the View drop-down list to view the fields by type, alphabetically, or schema.

To view the Layout panel, on the interactive toolbar, click the Rearrange fields on the report button.

The Layout panel shows the rows, columns, and measures used in the report. At the bottom is the Report Options button.

To keep only the 2003 data, on the canvas, right-click the 2003 column header, and then click Keep Only 2003.

To drill down to show the Quarters, on the canvas, double-click the 2003 column header.

To view the analysis as a chart, on the interactive toolbar click the Choose chart type button, and then click Column.

To view the report in table format, on the interactive toolbar, click the Switch to table format button.

Chart Types

To open the Country Performance heat grid, on the main toolbar, click the Open button.

Navigate to the Public folder, click the Up One Level button twice, and then double-click the Public folder.
Double-click the Steel Wheels folder, then click Country Performance (heat grid), and then click Open.

View the Country Performance heat grid.

The Country Performance heat grid shows Sales by Year and Quarter for each country. The colour coded squares provide a visual representation of the data.

The data for Belgium indicates sales have been consistently low, and sales for France have been fair. We can quickly see that France had a good fourth quarter in 2004, and Spain has had several good quarters.

To view the underlying details for a specific square, hover the cursor over the red square for Belgium, 2003, QTR2.

Click the Spain label.

The data for Spain is in colour, but the rest of the chart data is dimmed. The buttons to Keep Only, Exclude, and Clear Selections at the top of the heat grid enable you to include / exclude the data selected.

Drill into the data for Spain at the top of the heat grid, click the Keep Only button.
To view the Available Fields and Layout panels, on the interactive toolbar, click the Add more fields onto the report and Rearrange fields on the report buttons.

To see the sales data for specific product lines in Spain, from the Available Fields pane, select Line and drag it to the X Axis area in the Layout panel.

Sales for classic cars were best in Q1 of 2005, but they began to improve in the Q4 of 2004.

Drill down to the months, double-click the green square for Classic Cars, 2004, QTR4. December was the best month of the quarter.

Drill down to the Vendor, double-click the green square for December.

Classic Metal Creations was the highest performing vendor. You could continue drilling down, however, selecting Table will make sense for exporting the sales data.

On the report title bar, click the View As Table button.

On the report toolbar, click More actions and options > Export > To PDF, and then in the Export to PDF window, click Export.

Selecting PDF opens in a new browser window. You can save or print the PDF using the PDF toolbar.

Close the PDF browser window.
On the report toolbar click More actions and options > Reset.

Sales Analysis

The report will display Sales Revenue grouped by Territory and Product Line over time.

From the User Console Home Perspective, click Create New > Analysis Report.
In the Select Data Source window, click Steel Wheels: SteelWheelsSales, and then click OK.

Data Types

When you first open Analyzer, the available fields associated with the data source you select are displayed in the Available Fields panel.

The Available Fields panel consists of a list of measures and dimensions.

• Measures: consist of the numeric data that can be calculated, and are depicted with the ruler icon.

• Dimensions: are different aspects of the calculated data, and are depicted with the stacked squares icon. If a dimension has a number in parenthesis next to it, this indicates it has associated properties.

• Properties: are single data items related to a database object. The database schema associates one or more properties with each database entity. For example, the dimension ‘Product’ can have the properties: colour, shape, size, weight, etc..

From the Available Fields panel, select Country and drag it to the Rows drop zone on the Layout panel.

To remove Country, from the canvas:
· Click the Country header.
· Drag it to the lower right corner of the canvas.
· Drop it in the trashcan.

Add Sales to the report by dragging it to the Measures drop zone on the Layout panel.
From the Available Fields panel, double-click Territory.
Select Years and drag it to the Columns drop zone on the Layout panel.
Select Quarters and drag it to the Columns drop zone on the Layout panel. Drop Quarters below Years.

Select Line and drag it to the Rows drop zone on the Layout panel. Drop Line above Territory.

When you add columns and rows Analyzer dynamically aggregates the Measure to the required level.

To rearrange the fields, on the Layout panel, click Territory and drag it above Line.

Filters are used to restrict or limit the data that is presented in an analysis. When you create a filter for a text field you can select from a list of values or match a specific value by typing the value in the text box and specifying a constraint (Contains or Does not Contain).

When you filter on time periods you can choose a commonly used time period, select from a list, or select a range.

On the Interactive Toolbar, click the Filter button.

In the Available Fields panel, select Territory and drag it to the Filter panel.

To display only EMEA, in the Filter on Territory dialog box:
• Select: Select from a list.
• From the list of values, click EMEA.
• Click the right arrow to move EMEA to the Currently Included list.
• Click OK.

The Parameter Name field enables you to define the filter as a prompt.

In the Available Fields panel, right-click Years, and then select Filter.

To display only 2003, in the Filter on Years dialog box:
• Select: Select from a list.
• From the list of values, click 2003.
• Click the right arrow to move 2003 to the Currently Included list.
• Click OK.

In the analysis canvas, right-click Quarters, and then select Filter.

To display only the second quarter, in the Filter on Quarters dialog box, select: Select from a list.

From the list of values, click QTR2, then click the right arrow to move QTR2 to the Currently Included list, and then click OK.

On the analysis canvas, click Sales and drag it up to the Filter panel.
To display the Top 5 Product Lines with Sales Greater Than 10,000 in the Filter on Sales dialog box:
• In the text box for Sales Greater Than, type: 10000.
• Click the Top 10, etc. check box..
• In the numeric value box, type 5.
• Click OK.

On the analysis canvas, right-click the QTR2 header for 2003, and then select Show All Quarters.
Remove the Sales is greater than 10000 filter.

Applying Conditional Formatting

Conditional formatting means that cells within the analysis will be physically affected by the data they contain. The most common form of conditional formatting is stoplight reporting, where cell backgrounds are coloured red, green, or yellow depending on user-defined thresholds.

Analyzer provides the following methods for conditionally formatting numeric data:

• Color Scale

• Data Bar

• Trend Arrow

Right-click one of the Sales headers, and then select Conditional Formatting > Color Scale: Green-Yellow-Red.

Remove the conditional formatting, right-click one of the Sales headers, and then deselect Conditional Formatting > Green-Yellow-Red.

Grand totals and subtotals summarize detail row or column values. You can choose to summarize the data in the following ways:

• Aggregate

• Sum

• Average

• Max

• Min

Remove all Filters.
In the analysis details, right-click one of the Sales headers, then select Subtotals (Sums, Averages, etc.).

Select Average, and then click OK.

In the analysis details, right-click the Territory header, and then select Show Subtotals.

Subtotals do not appear in the analysis, until the dimension selected.

To show grand totals for columns and rows, from the Layout panel: Click Report Options.
In the Report Options window, select Show Grand Totals for Rows and Show Grand Totals for Columns.

Click OK.

User Defined Measure

Analyzer allows you to create three types of measures directly within an analysis:

• Percent of, rank, running sum, or percent of running sum

• Calculated Measures

• Trend Measures

So we're going to display the percent of sales for each product line, and add a simple calculated measure to show the sales plus 6% tax.

Remove all Totals, Subtotals & Averages for rows & columns.
Apply 2 filters:

• Territory: EMEA

• Year 2003

Right-click one of the Sales headers, then select User Defined Measure > % of, Rank, Running Sum….

Select: % of Sales.

Click Next.
Click the drop-down arrow for Decimal Places, select 0.

Click: Done.

Calculated Measure

In the analysis details right-click one of the Sales headers, then select User Defined Measure > Create Calculated Measure.

The left panel lists the measure fields available to use in the calculation. The Selection pane is where you write the MDX expression to calculate the new measure.

In the Name text box, type Sales + 6% Tax.
To specify the numeric format:
Click the drop-down arrow for Format.

• Select Currency.

• Click the drop-down arrow for Decimal Places.

• Select 0.

To multiply Sales by 1.06, in the formula pane:

• Click to the right of sales.

• Type: * 1.06.

• Click OK.

Drill-Through

To create reports based on specific measure data, you can implement drill through links in Analyzer. This will turn all non-calculated measures into links which, when clicked, open a data table that enables you to quickly view more details for that data point.

Remove the following columns:

• Sales + Tax 6%

• % of Sales

Enable drill through links for Sales, from the Layout panel:

• Click Report Options.

• Select Show drill-through links on Measure cells.

• Click OK.

Drill through to the supporting data, in the analysis details, click the value for EMEA, Classic Cars, 2003, QTR1 ($96,678).

Actions & Options

Export Options

Analyzer provides several options for exporting your analysis. You can export the analysis as a PDF file which launches the analysis in a new window. From there you can save or print the PDF file. You can export the analysis to Excel, which opens the analysis in a new Excel window.

You can download the analysis data in CSV format. When you download data in the CSV format you get numbers with the full precision available. This way you avoid any rounding errors when you continue to work with your data in Excel. The export options are available from the Interactive Toolbar by clicking the More actions and options button.