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. 







    • Sticky Posts

    • Tip of the Week - Spot Risky Sales with Conditional Formatting

      In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to apply

    • Recent Topics

    • Question about upgrade and storage space Zoho Notebook

      After upgarding my Zoho Notebook plan, I am running into the following issue. I just upgraded from a free Zoho Notebook subscription to Pro Lite after I got a notification in my Window Zoho Notebook desktop app saying that I had run out of space. However,

    • Printing to a brother label maker

      I see allot of really old unanswered posts asking how to print to a label maker from a zoho creator app. Has their been any progress on providing the capability to create a customized height & width page or print template or whatever to print labels?

    • Sync desktop folders instantly with WorkDrive TrueSync (Beta)

      Keeping your important files backed up and accessible has never been easier! With WorkDrive desktop app (TrueSync), you can now automatically sync specific desktop folders to WorkDrive Web, ensuring seamless, real-time updates across devices. Important:

    • Track online, in-office, and client location meetings separately with the new meeting venue option

      Hello everyone! We’re excited to announce meeting enhancements in Zoho CRM that bring more clarity and structure to how meetings are categorized. You can now specify the meeting venue to clearly indicate whether a meeting is being held online, at the

    • Announcing new features in Trident for Mac (1.32.0)

      Hello everyone! We’re excited to introduce the latest updates to Trident, which are designed to reinforce email security and protect your inbox from evolving threats. Let’s take a quick look at what’s new. Deliver quarantined emails. Organization admins

    • Marketing Tip #5: Improve store speed with optimized images

      Slow-loading websites can turn visitors away. One of the biggest culprits? Large, uncompressed images. By optimizing your images, your store loads faster and creates a smoother shopping experience leading to higher sales. It also indirectly improves SEO.

    • SMS to customers from within Bigin

      Hi All, Is there anyone else crying out for Bigin SMS capability to send an SMS to customers directly from the Bigin interface? We have inbuilt telephony already with call recordings which works well. What's lacking is the ability to send and receive

    • Admins cannot see each others' Scheduled Reports?!

      Very frustrating that as an admin I cannot see what my reports my fellow admins have created and scheduled.  After asking about this on the help chat, I was told the issue is trust and security.  By giving someone Admin status, it means we trust them with those responsibilities. Please change this, it is not a good process to have to bother other users to change a report or change users within a report.

    • Writer update results in BitDefender blocking it as malware

      After updating Writer to latest update, Bitdefender blocked the app and writer no longer runs.

    • Missing Import Options

      Hello, do I miss something or is there no space import option inside of this application? In ClickUp, you can import from every common application. We don't want to go through every page and export them one by one. That wastes time. We want to centralize

    • Zoho CRM Portal Field Level Permission Issue

      Hi Support Team, I am using the Zoho CRM Portal and configuring field-level editing permissions. However, we are unable to restrict portal users from editing certain fields. We have created a portal and provided View and Edit (Shared Only) access for

    • Custom Fonts in Zoho CRM Template Builder

      Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities if

    • Collaboration with customers made easy with Zoom Meeting and Zoho Desk integration

      Hello everyone! We are happy to announce that you can now integrate your Zoho Desk account with Zoom Meeting. The integration bridges the gap between digital communication and human connection, empowering teams to deliver timely support when it matters

    • CRM Canvas - Upload Attachments

      I am in the process of changing my screens to Canvas.  On one screen, I have tabs with related lists, one of which is attachments.  There doesn't appear to be a way to upload documents though.  Am I missing something really obvious?  Does anyone have

    • TrueSync regularly filling up my local disk

      Seems that WorkDrive's TrueSync randomly starts filling up my local hard drive space. None of the folders have been set as "Make Offline" but still it seems to randomly start making file offline. The settings of the app is so minimal and is of no real

    • Kaizen #194 : Trigger Client Script via Custom buttons

      Hello everyone! Welcome back to another interesting and useful Kaizen post. We know that Client Scripts can be triggered with Canvas buttons and we discussed this with a use case in Kaizen#180. Today, let us discuss how to trigger Client Script when a

    • [Webinar] A recap of Zoho Writer in 2025

      Hi Zoho Writer users, We're excited to announce Zoho Writer's webinar for January 2026: A recap of Zoho Writer in 2025. This webinar will provide a recap of the features and enhancements we added in 2025 to enhance your productivity. Choose your preferred

    • Picklist field shows "none" as default

      Hello, Is there an option to avoid showing "none" as the default value in a picklist field? I also don't want to see any option displayed. My expectation is to have a blank bar, and then when I display the drop-down list, I can choose whichever I wa

    • Stage-probability mapping feature in custom module

      Hi, I'm building a custom module for manage projects. I would like to implement the stage-probability feature that Potentials has. Is this possible?

    • Field Description is very small

      Hello, The field Description in the activity is very small. Why don't try open a new window, or a bigger popup, or increase the width of the "popup". Example:

    • StatusIQ

      Please add StatusIQ to data sources. We using site24x7 and StatusIQ together and site24x7 integration is already there. Thanks and regards, Torsten

    • In Zoho People, the Operations buttons are frequently not visible or do not appear consistently.

      In Zoho People, the Operations buttons are frequently not visible or do not appear consistently. We request you to please investigate and address this issue, as it is affecting daily HR operations and user access.

    • Marketing Tip #14: Increase cart value with product bundles

      Bundling products is a great way to increase average order value while giving customers more convenience. Think “camera + tripod + memory card” or “soap + lotion + bath salts.” Bundles make shopping easier and feel like a better deal. It’s a win-win for

    • Problem with Workdrive folders

      I'm having a problem a problem accessing files in a Zoho work drive folder when using the Zoho writer app. The problem folder appears grayed out in the Zoho work drive window in both the online and writer application. However I can open the folder in

    • Add RTL and Hebrew Support for Candidate Portal (and Other Zoho Recruit Portals)

      Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to set the Candidate Portal to be Right-to-Left (RTL) and in Hebrew, similar to the existing functionality for the Career Site. Currently, when we set the Career Site

    • Pre-orders at Zoho Commerce

      We plan to have regular producs that are avaliable for purchase now and we plan to have products that will be avaliable in 2-4 weeks. How we can take the pre-orders for these products? We need to take the money for the product now, but the delivery will

    • Zoho Books' 2025 Wrapped

      Before we turn the page to a new year, it’s time to revisit the updates that made financial management simpler and more intuitive. This annual roundup brings together the most impactful features and enhancements we delivered in 2025, offering a clear

    • Can multiple agents be assigned to one ticket on purpose?

      Is it possible to assign one ticket to two or more agents at a time? I would like the option to have multiple people working on one ticket so that the same ticket is viewable for those agents on their list of pending tickets. Is something like this currently

    • Edit default "We are here to help you" text in chat SalesIQ widget

      Does anyone know how this text can be edited? I can't find it anywhere in settings. Thanks!

    • Can't change form's original name in URL

      Hi all, I have been duplicating + editing forms for jobs regarding the same department to maintain formatting + styling. The issue I've not run into is because I've duplicated it from an existing form, the URL doesn't seem to want to update with the new

    • Feature Request: Sync Leave Tracker to Zoho Mail / Calendar or provide option to download information

      Zoho Leave Tracker offers the option to sync the leave Calendar to Microsoft 365 and Google Calendar. Adding an option to sync to Zoho-Mail Calendar would avoid duplication and add significant value for users. An alternative would be to allow users to

    • Compensation | Salary Packages - Hourly Wage Needed

      The US Bureau of Labor Statistics says 55.7% of all workers in the US are paid by the hour. I don't know how that compares to the rest of the world, but I would think that this alone would justify the need for having an hourly-based salary package option.

    • Multiple currencies - doesn’t seem to work for site visitors / customers

      I am trying to understand how the multiple currency feature works from the perspective of the website visitor who is shopping on my Zoho Commerce site. My site’s base currency is US Dollars (USD) but my store is for customers in Costa Rica and I would

    • Archiving Contacts

      How do I archive a list of contacts, or individual contacts?

    • How do people handle using Outlook and Zoho Project calendar at the same time?

      We have an ongoing problem in our organisation where we use Zoho Projects to plan all of our projects tasks and that also allows us to look forward using the workload report to see which of our consultants are overstretched etc and which are available.

    • 10GB Email Storage Limits in Zoho CRM

      We’ve had Zoho One for almost 5 years and have always synced our emails from Gmail via IMAP… As of late, we’ve run into issues with our emails not syncing, due to being over the 10GB storage cap… What’s very odd is that we haven’t changed a thing? I know

    • Performance is degrading

      We have used Mail and Cliq for about three years now. I used to use both on the browser. Both have, over the past 6 months, had a severe degradation in performance. I switched to desktop email, which appeared to improve things somewhat, although initial

    • How to set page defaults in zoho writer?

      hi, everytime i open the zoho writer i have to change the default page settings to - A4 from letter, margins to narrow and header and footer to 0. I cannot set this as default as that option is grayed out! so I am unable to click it. I saved the document

    • Unable to Send Different Email Templates for Different Documents in Zoho Sign

      Hello Zoho Community, I am facing a limitation with Zoho Sign regarding email notifications sent to customers when a document is sent for signing. Currently, whenever I send any template/document for signing, the email notification that goes to the customer

    • Enable History Tracking for Picklist Values Not Available

      When I create a custom picklist field in Deals, the "Enable History Tracking for Picklist Values" option is not available in the Edit Properties area of the picklist. When I create a picklist in any other Module, that option is available. Is there a specific reason why this isn't available for fields in the Deals Module?

    • Next Page