Report of the Week - Comparative Analysis with Top N Items

Previously, we saw how to create a report for Top N Items in Each Category . Now let's see how to do comparative analysis along with top values with the following reports as examples.

Comparing Current and Previous Month Customers' Invoices - This report compares two sets of values and gets the top and bottom N results.
Sales in Percentage against Top Sales - This report gets the percentage of all values in comparison with the top value.

Report 1: Comparing Current and Previous Month Customers' Invoices

This report ranks the customers whose invoices have grown in comparison with the previous month. This helps you identify customers who tend to purchase more from you and also the customers who will purchase less.

Let's see how to create the above report.

Create Aggregate Formulas
Create Report

Create Aggregate Formulas

Create the following Aggregate formulas by clicking Add > Aggregate Formula to get the required data.

Current Month Sales
Previous Month Sales
Difference in Sales between current and Previous Month
Rank Sales

Current Month Sales

The following formula gives you the current month sales.

Current Month Sales

sum(if(absmonth( "Sales"."Date" ) = absmonth(today()), "Sales"."Sales ", 0 ))

Previous Month Sales

The following formula gives you the previous month sales.

Previous Month Sales

sum(if(absmonth( "Sales"."Date" ) = absmonth(add_month(today(), -1)), "Sales"."Sales ", 0 ))

Compare Current and Previous Month Sales

Now compare the current and previous month sales using the following formula, which gives the difference between current and previous month sales.

Sales Diff Previous Month

"Sales"."Previous Month Sales" - "Sales"."Current Month Sales"

Rank Sales

Add an aggregate formula to rank the sales in ascending order. From this, you can get the botton N items by sales.

Rank of Sales Asc:

Rank( "Sales"."Sales Diff Previous Month" ,' asc ')

Have another aggregate formula to rank sales in descending order to get the top N items by sales.

Rank of Sales Desc:

Rank( "Sales"."Sales Diff Previous Month" ,' desc ')

Create Report

Now, the necessary reports can be created using the above aggregate formulas.

Open the Pivot Table designer.
Drop the columns as given below.

Row - Customer Name with Actual
Data - Current Month Sales, Previous Month Sales and Sales Diff Previous Month with Actual

Filter the Top or Bottom 5 Customers by adding the Rank of Sales columns in the Filters tab.

Add Rank of Sales - Desc and filter Range below 6 to filter top 5 customers.
Add Rank of Sales - Asc and filter Range below 6 to filter bottom 5 customers.

The report will be generated as below. Hide the Total.
Open Dashboard designer and add both the reports.

Report 2: Comparing Sales in Percentage against Top Sales

As the second example, let us see how to create a report that compares each value against the top value. For example, comparing each sales person's sales with the top performing sales person's sales.

Find the Top Sales

Create an aggregate formula to identify the top sales using the below formula.

First(Sum( "Store Sales"."Sales" ))

The above formula will calculate sales for the dimension in your report (Region, in this example) and return the top value for each section.

The above report displays the top sales made by the salespersons in each region.

Calculating Sales in Percentage against Top Sales

Now you can calculate the Sales in Percentage against Top Sales using the Report Formula. To invoke the Report Formula dialog, click the highlighted icon.