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

    • IP Address Blocked

      Hi Team. Hope you're doing good. I'm unable to access Zoho with my IP address. I have to use VPN to access it. IP: 39.44.12.153 This is happening with all the Zoho products, the admin console and the mail.

    • Outdated state in mexico

      Hello Zoho team, the drop down to add the state for customers, when they introduce their state in mexico has a city named “Distrito Federal” that name changed many years ago to “ciudad de mexico”. could you please update this so my clients can find the

    • Possible to generate/download Quote PDF using REST API?

      See title. Is there any way after a quote has been created to export to a PDF using a specified template and then download it? Seems like something that should be doable. Is this not supported in the API v2.0?

    • Creating an invoice to be paid in two installments?

      Hi there, I own a small Photographic Services business and have not been able to find a way to fit my billing system into Zoho, or any other Accounting software. The way my payments work is: 1. Customer pays 50% of total price of service to secure their

    • Bug in allowing the user to buy out of stock items

      Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

    • Replace Lookup fields ID value with their actual name and adding inormation from subforms

      Hi everyone,  I wanted to see if someone smarter than me has managed to find any solutions to two problems we have. I will explain both below.  To start we are syncing data from Zoho CRM to Zoho Analytics and I will use the Sales Order module when giving

    • Can a Zoho Sites page be embedded into another website (outside Zoho)

      Hi All, We have a request from a client - they'd like to take one of our information pages created in Zoho Sites and embed it into their own website? I was told through an email with Zoho that this was possible >>Thank you for your patience regarding

    • Bug in allowing the user to buy out of stock items

      Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

    • Transition Criteria Appearing on Blueprint Transitions

      On Monday, Sept. 8th, the Transition criteria started appearing on our Blueprints when users hover over a Transition button. See image. We contacted Zoho support because it's confusing our users (there's really no reason for them to see it), but we haven't

    • Zoho CRM Sales Targets for Individual Salespeople

      Our organistion has salespeople that are allocated to different regions and have different annual sales targets as a result. I am building an CRM analytics dashboard for the sales team, which will display a target meter for the logged in salesperson.

    • Transfer all Related Data to new Account Owner

      Currently when I change the account Owner I only see the option to change only the open deals But I want the new account owner to take over all the related modules and all the deal stages Is it not possible right now? Am I missing something? Do I really

    • Can i connect 2 instagram accounts to 1 brand?

      Can i connect 2 instagram accounts to 1 brand? Or Do i need to create 2 brands for that? also under what subscription package will this apply?

    • Add an external email to the allowed list

      I need to allow an external email to raise a ticket on Manage Engine ServiceDesk Plus. The external email is a outside of our domain and is not a domain user. The email address that needs to be allowed to raise a ticket request is noreply@formsatack.com.

    • How to Calculate MTTR (Mean Time to Resolve)

      We want to calculate MTTR (Mean Time to Resolve) in our Zoho Analytics report under Tickets. Currently, we are using the following fields: Ticket ID Ticket Created Time Ticket Closed Time Ticket On Hold Time We are planning to calculate MTTR (in days)

    • How to export project tasks, including the comments

      Hi, how can I export the project tasks, whereby I can also see the comments associated to a specific task? The use-case is that often we use comments to discuss or update a task related ideas. I would like to export the tasks, where we can also see the

    • Unable to see Zoho contacts in Zoho app on ios

      Hi Support Team, I am a new user, I have created my account and installed zohomail app on iOS 16 which works. I was also able to import my Gmail contacts into Zoho Contacts, which I can see. The problem is that I can’t see these imported cobalts in Zohomail

    • Does Zoho Sheet Supports https://n8n.io ?

      Does Zoho Sheet Supports https://n8n.io ? If not, can we take this as an idea and deploy in future please? Thanks

    • Bigin Android app update: User management

      Hello everyone! In the most recent Bigin Android app update, we have brought in support for the 'Users and Controls' section. You can now manage the users in your organization within the mobile app. There are three tabs in the 'Users and Controls' section:

    • Share records with your customers and let them track their statuses in real time.

      Greetings, I hope everyone is doing well! We're excited to introduce the external sharing feature for pipeline records. This new enhancement enables you to share pipeline records with your customers via a shareable link and thereby track the status of

    • Live webinar: Discover Zoho Show: A complete walkthrough

      Hello everyone, We’re excited to invite you to our upcoming live webinar, Discover Zoho Show: A Complete Walkthrough. Whether you’re just getting started with Show or eager to explore advanced capabilities, this session will show you useful tips and features

    • Deal Stage component/widget/whatever it is... event

      Deal Stages I am trying to access the event and value of this component. I can do it by changing the Stage field but users can also change a Deal Stage via this component and I need to be able to capture both values. Clicking on 'Verbal' for instance,

    • Create advanced slideshows with hybrid reports using Zoho Projects Plus

      Are your quarterly meetings coming up? It’s time to pull up metrics, generate reports, and juggle between slides yet again. While this may be easier for smaller projects, large organizations that run multiple projects may experience the pressure when

    • Add an option to disable ZIA suggestions

      Currently, ZIA in Zoho Inventory automatically provides suggestions, such as sending order confirmation emails. However, there is no way to disable this feature. In our case, orders are automatically created by customers, and we’ve built a custom workflow

    • Email Integration - Zoho CRM - OAuth and IMAP

      Hello, We are attempting to integrate our Microsoft 365 email with Zoho CRM. We are using the documentation at Email Configuration for IMAP and POP3 (zoho.com) We use Microsoft 365 and per their recommendations (and requirements) for secure email we have

    • Formula field with IF statement based on picklist field and string output to copy/paste in multi-line field via function

      Hello there, I am working on a formula field based on a 3-item picklist field (i.e. *empty value*, 'Progress payment', 'Letter of credit'). Depending on the picked item, the formula field shall give a specific multi-line string (say 'XXX' in case of 'Progress

    • CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive

      Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files

    • Zoho CRMの流入元について

      Zoho CRMとZoho formsを連携し、 formsで作成したフォームをサイトに埋め込み運用中です。 UTMパラメータの取得をformsを行い、Zoho CRMの見込み客タブにカスタム項目で反映される状況になっています。 広告に関してはUTMパラメータで取得できているため問題ないのですが、オーガニック流入でフォーム送信の場合も計測したいです。メールやGoogle、Yahoo、directなどの流入元のチャネルが反映されるようにしたいのですが、どのように設定したら良いでしょうか。 また、

    • In App Auto Refresh/Update Features

      Hi,    I am trying to use Zoho Creator for Restaurant management. While using the android apps, I reliased the apps would not auto refresh if there is new entries i.e new kitchen order ticket (KOT) from other users.   The apps does received notification but would not auto refresh, users required to refresh the apps manually in order to see the new KOT in the apps.    I am wondering why this features is not implemented? Or is this feature being considered to be implemented in the future? With the

    • Error While Sign in on Zoho Work Drive

      Dear Team, I hope this email finds you well. I have recently created a Zoho account and started using it. But while I am trying to log in to Zoho work drive it won't log me in its crashing every time I try it. I have tried it on android app, phone browser

    • Choosing a portal option and the "Unified customer portal"?

      I am trialling Zoho to replace various existing systems, one of which is a customer portal. Our portal allows clients to add and edit bookings, complete forms, manage their subscriptions and edit some CRM info. I am trying to understand how I might best

    • Elevate your CX delivery using CommandCenter 2.0: Simplified builder; seamless orchestration

      Most businesses want to create memorable customer experiences—but they often find it hard to keep them smooth, especially as they grow. To achieve a state of flow across their processes, teams often stitch together a series of automations using Workflow

    • Unified Directory : How to Access ?

      I signed in to Zoho One this morning and was met with the pop up about the upgraded directory (yay!) I watched the video and pressed "Get Started" ... and it took me back to the standard interface. How do I actually access the new portal/directory ?

    • Translation support expanded for Modules, Subforms and Related Lists

      Hello Everyone!   The translation feature enables organizations to translate certain values in their CRM interface into different languages. Previously, the only values that could be translated were picklist values and field names. However, we have extended

    • Unified task view

      Possible to enable the unified task view in Trident, that is currently available in Mail?

    • Bigin, more powerful than ever on iOS 26, iPadOS 26, macOS Tahoe, and watchOS 26.

      Hot on the heels of Apple’s latest OS updates, we’ve rolled out several enhancements and features designed to help you get the most from your Apple devices. Enjoy a refined user experience with smoother navigation and a more content-focused Liquid Glass

    • Importing data into Assets

      So we have a module in Zoho CRM called customers equipments. It links to customers modules, accounts (if needed) and products. I made a sample export and created extra fields in zoho fsm assets module. The import fails. Could not find a matching parent

    • Allow instruction field in Job Sheets

      Hello, I would like to know if it is possible to have an instruction field (multi line text) in a job sheet or if there is a workaround to be able to do it. Currently we are pretty limited in terms of fields in job sheets which makes it a bit of a struggle

    • Streamlining Work Order Automation with Zoho Projects, Writer & WorkDrive

      Hello Community, Here is the first post in 'Integration & Automation' Series. Use Case :: Create, Merge, Sign & Store Documents in Zoho WorkDrive. Scenario :: You have a standard Work Order template created in Zoho Writer. When a task status is chosen

    • The dimensions of multilingual power

      Hola, saludos de Zoho Desk. Bonjour, salutations de Zoho Desk. Hallo, Grüße von Zoho Desk. Ciao, saluti da Zoho Desk. Olá, saudações da Zoho Desk. வணக்கம், Zoho Desk இலிருந்து வாழ்த்துகள். 你好，来自 Zoho Desk 的问候。 مرحباً، تحيات من Zoho Desk. नमस्ते, Zoho

    • Multi-line address lines

      How can I enter and migrate the following 123 state street Suite 2 Into a contact address. For Salesforce imports, a CR between the information works. The ZOHO migration tool just ignores it. Plus, I can't seem to even enter it on the standard entry screen.

    • Next Page