Report of the Week - Comparative Analysis with Top N Items

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.

  1. Open the Pivot Table designer. 
  2. 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
  3. 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. 
  4. The report will be generated as below. Hide the Total. 



  5. 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. 


Use the below formula to calculate the Sales in Percentage against Top Sales.  


 

( "1. Sales" / "2. Top Sales" )* 100




A report will be generated. 




Hide the Totals using the Show/Hide button and customize the Theme as needed. 







    • Recent Topics

    • Create Lead Button in Zoho CRM Dashboard

      Right now to create Leads in the CRM our team is going into the Lead module, selecting the "Create Lead" button, then building out the lead. Is there anyway to add the "Create Lead" button or some sort of short cut to the Zoho CRM Dashboard to cut out

    • Searching customer field

      Hello, When entering a receipt, we select customer information. The customer information is synced with Zoho CRM. However, we can't find the customer information because it searches for words that begin with the entered value. It needs to search for words

    • Introducing Version-3 APIs - Explore New APIs & Enhancements

      Happy to announce the release of Version 3 (V3) APIs with an easy to use interface, new APIs, and more examples to help you understand and access the APIs better. V3 APIs can be accessed through our new link, where you can explore our complete documentation,

    • Zoho CRM Tracking Google Enhanced Conversions

      Can anyone @Zoho, consultants, or users help me understand if Zoho CRM is going to support Google's Enhanced Conversions? I included some information from Google below about it. We use Google Adwords for our pay per click advertising for lead generation,

    • zoho click, and nord VPN

      Unfortunately, we've been having problems with Zoho Click, where essentially the line cuts off after about a minute's worth of conversation every time we are on VPN. Is there a way we can change this within the settings so it does not cut the line off

    • Connecting Portals from different Zoho apps

      Hi, I note that Zoho has functionality for customer portals for several of the Zoho apps, like CRM, Projects, Desk etc. Is there any way to connect these portals?  It would be great if we could give our customers access to a portal in which they could

    • Billing Management: #5 Usage Billing

      After understanding the nuances of Advance Billing and Retainers, we will explore one of the booming billing models. Long ago, villagers drew water from a shared well in a small village. The well was a lifeline for the entire community. Ravi, the well

    • Function #10: Update item prices automatically based on the last transaction created

      In businesses, item prices are not always fixed and can fluctuate due to various factors. If you find yourself manually adjusting the item rates every time they change, we have the ideal time-saving solution for you. In today's post, we bring you custom

    • Inventory Adjustments

      Hi, How to transfer the material from one head to another ? Like materials purchased for manufacturing the laptop need to transfer from consumption inventory (Quantity of raw materials reduced) to destination inventory ( Quantity of Laptop increased)

    • Zoho CRM Community Digest - Aug 2025 | Part 1

      Hey everyone! The first half of August went by, and we have a few announcements and some good noteworthy discussions. So, let's take a look at them! Product Updates: Introducing Connected Records feature: Zoho CRM’s Next-Gen UI now includes Connected

    • Please make it easier to Pause syncing

      right now it takes 3 clicks to get there. sounds silly, but can you make it just 2 clicks to get it done instead? thats how dropbox does it, 2 clicks to pause instead of 3.

    • How to create a Zoho CRM report with 2 child modules

      Hi all, Is it possible to create a Zoho CRM report or chart with 2 child modules? After I add the first child module, the + button only adds another parent module. It won't let me add multiple child modules at once. We don't have Zoho Analytics and would

    • SalesIQとPageSenseの利用について

      初めての投稿で場違いだったらすいません。 弊社ではSalesIQを運用しているのですが、追加でPageSenseの導入もしたいと現場からの声があります。 両サービスともクッキー同意バナーが必要なサービスなのですが 弊社では同意無しに情報はとりませんという方針なので 2つ入れると2つバナーを出す必要がでてきます・・・ 両サービスを運用されてる方があれば運用状況とか教えてほしいです。 PageSenseについては詳細まで機能を理解してないなかでの質問です。

    • How to integrate Zoho Forms with Zoho CRM on Standard Plan

      Hello Zoho Support Team, I am using the Standard Zoho Forms plan (USD 30/user) and I would like to integrate Zoho Forms with Zoho CRM so that certain fields in my forms can be automatically prefilled using data from Deals in CRM. Specifically, I want

    • Export PDF File Name

      Is it possible to change the default Zoho .pdf naming scheme for inventory items like quotations? Would like to use the the Subject as the default quote name. Is this possible?

    • How to change the from address from 'no reply' for an email template in CRM

      Hi, We have our CRM set up with the from field as sales@XXX. I have just created a series of email templates and sent a test and they are sending from noreply@zoho I have tried searching for how to change the email template but don't have the options

    • Zoho CRM Client Script - SetCriteria in lookup Field

      Hello All One More Zoho CRM Client Script Tips & Trick. Now you can Set the Criteria on Your lookup in zoho CRM, It Comes With a Create Page, Edit Page, and Details Page (Standard). Example:- We have a Room Module that includes Room Name, Status, Campus,

    • Kaizen #71 - Client Script ZDKs for Detail (Canvas) Page

      Hello everyone! Welcome back to another interesting Kaizen post. In this post, we can discuss Client Script ZDKs support for Detail (Canvas) Page. What is Detail (Canvas) Page? A Detail(Canvas) Page allows you to customize the record detail page to your

    • Rich-text fields in Zoho CRM

      Hello everyone, We're thrilled to announce an important enhancement that will significantly enhance the readability and formatting capabilities of your information: rich text options for multi-line fields. With this update, you can now enjoy a more versatile

    • Zoho Reports Duplicating Entries

      I have a custom costing tab with a table where we entre invoices. These are under a Heading (PO Subject) and notes added in the form with different line items. In the reports, I have organised the report to group per PO Subject, with the total of the

    • Validation Rule Not Working for Mandatory Field in Zoho Blueprint

      As a Zoho user, we created a validation rule for a specific field. However, we noticed that when we made the same field mandatory within a Blueprint, the validation rule we defined did not work. When we reported this issue to Zoho Support, they stated

    • Notes Issues

      Been having issues with Notes in the CRM. Yesterday it wasn't showing the notes, but it got resolved after a few minutes., Now I have been having a hard time saving notes the whole day. Notes can't be saved by the save button. it's grayed out or not grayed

    • Export from Contacts module to Products module in Zoho CRM

      Good afternoon, I would like to send a number of contact info from the Contacts module into the customized module (tickets to an event) in one operation. I have selected several contacts in the Contact module (people who I have labelled as people I want

    • Zoho Commerce

      Hi, I have zoho one and use Zoho Books. I am very interested in Zoho Commerce , especially with how all is integrated but have a question. I do not want my store to show prices for customers that are not log in. Is there a way to hide the prices if not

    • Can’t receive emailI c

      I have generated a basic for but when I submit it I don’t get a email, I’ve been in the settings and tested me email, all appears correct, can you please help me

    • Data Capture for Historical Activity (Especially One Lead Downloading Variois reports without Overwriting the info)

      Is there a better way in Zoho CRM to capture and archive a lead’s historical activity—specifically whenever they download reports—so that the data is stored without being overwritten?”

    • Client Script - Updating Field Value in Detail Page of a Lead

      Hello, I'm trying to use Client Script To enrich some data of the Lead when one of my User fill the "City" field in the detail page of the Lead. This is my Script: log (value); var response = ZDK.Apps.CRM.Functions.execute("getInfoCitta", { "nomeCitta":

    • Auto shapes in Zoho sheet.

      Does Zoho sheet supports inserting auto shapes (rectangle, circle...). I did not see any option to do so.  If its not supported currently, is there any plans on bring in this features. Any timelines ?

    • I Can't Clone Webinar that I Co-Organize

      How do i get our account admin to give me permission to clone our webinars? I am a co-organizer

    • How to get the call recording external ID via desk API

      I have enabled phonbridge integration with Zoom Call. I am trying to access the call recording in Zoom by calling Zoom API. I have built a Desk workflow to trigger on a new call, to call a custom function. when calling the API, the response doesn't contain

    • Can't View Project Names in Mobile App

      I can't view project names on PO's in the app, nor can I add that as a viewable PDF field in inventory on the computer. I've attached screenshots showing that in the mobile version whether you are on the PO, editing the PO, or viewing the PO line items,

    • How do you print a refund check to customer?

      Maybe this is a dumb question, but how does anyone print a refund check to a customer? We cant find anywhere to either just print a check and pick a customer, or where to do so from a credit note.

    • Notebook

      I have purchased the monthly pro subscription of Notebook. But it does not support my XP-Pen to write something in it. So it is not useful to me. Hence I am requesting you to help me to discontinue this subscription.

    • Domain Mapping & Image Publishing Issues on Zoho Sites

      Hello, I am facing two issues with my Zoho Sites account: 1. Images not visible after publishing. 2. Domain mapping error: "Domain already exists". I am a paid customer. Please connect me with Live Chat Support or Zoho Assist so I can show my issue

    • Prevent duplicate with custom fields?

      I was wondering something about custom field/custom modules in Zoho Desk. For some reason you can make a custom field mandatory but not unique? For example, if I create a custom module to manage equipment and renewal and make a field serial number no

    • Two currencies

      More and more I am finding that internattional payments' fees are unpredictable. I would like, on my invoices that are in a foreign currency (eg. USD$ or EUR€) for there to be a GBP£ TOTAL display alongside the invoice's currency total. This would make

    • Automatic Matching from Bank Statements / Feeds

      Is it possible to have transactions from a feed or bank statement automatically match when certain criteria are met? My use case, which is pretty broadly applicable, is e-commerce transactions for merchant services accounts (clearing accounts). In these

    • Generate leads from instagram

      hello i have question. If connect instagram using zoho social, it is possible to get lead from instagram? example if someone send me direct message or comment on my post and then they generate to lead

    • Where is the desktop app for Zoho Projects???

      As a project manager, I need a desktop app for the projects I manage. Yes, there's the web app, which is AWESOME for cross browser and platform compatibility... but I need a real desktop app for Projects that allow me to enter offline information where

    • How to Automate Monthly PDF Reports with Filters in Zoho Creator

      Hi everyone, I’m trying to build an automated monthly reporting process in Zoho Creator and would appreciate suggestions or best practices from anyone who has done something similar. What I’m trying to do: I have a form called New_Customer with fields

    • Next Page