Report of the Week - Warehouse wise Inventory Aging Report over Finance Data

Report of the Week - Warehouse wise Inventory Aging Report over Finance Data

Slow moving stocks may lead to issues like expiry (in case of food items), obsolescence, and high holding costs. That's why you need the Inventory Aging Report. It helps you maintain a healthy business by tracking how quickly your inventory moves. It identifies the slow and fast moving inventory, and also lets you plan your warehouse capacity.




This report is done over a SQL query based on Advanced Analytics for Zoho Finance data. You can modify the query to suit your data structure.

Follow the below steps to do this.
  1. Query Table - Stock In Hand
  2. Query Table - Inventory Age Tier
  3. Pivot View - Inventory Aging Report

Query Table - Stock In Hand

This tracks the stock in hand by joining different tables.


  SELECT

    /*sum of Quantity On Hand from below query*/

  T1.*

SUM (T1. Quantity ) OVER( PARTITION BY T1. "Product ID" , T1. "Warehouse ID" ) AS 'Quantity On Hand',

    /*calculate  cumulative sum of items count in reverse order until it's equal or more that Quantity On Hand*/

 sum(if ( T1 . "In/Out"    = 'In' , T1 . Quantity , 0 )) OVER(PARTITION    BY    T1 . "Product ID" , T1. "Warehouse ID"    ORDER BY    T1. "Transaction Date"    DESC ) "Reverse Cumulative Total"

/*stock in hand from Stock In Flow*/

FROM (SELECT

 'In' "In/Out",

"Transaction Date",

"Warehouse ID",

 "Product ID",

sum ( "Quantity In") "Quantity",

"Price (BCY)" "Purchase Price"

FROM     "Stock In Flow Table"

WHERE "Stock In Flow Table" . "EntityType"    != 'transfer_order'

GROUP BY     1,

 2,

 3,

4,

6

UNION ALL

     /*stock in hand from Stock Out Flow*/

SELECT

  'Out',

  "Transaction Date",

 "Warehouse ID",

 "Product ID",

  -1 * sum ( "Quantity Out" ),

  null

FROM     "Stock Out Flow Table"

WHERE "Stock Out Flow Table" . "EntityType"     != 'transfer_order'

GROUP BY    1,

 2,

 3,

 4

UNION ALL

  /*stock in hand from Transfer Order*/

  SELECT

' In',

 "Date",

"Warehouse ID",

 "Product ID",

  sum(if( "Transfer Order". "Status"    = 'transferred' , "Transferred Quantity" , 0 )),

if ( "Transfer Order" . "Status" = 'transferred' , "Cost Price" , null )

FROM     "Transfer Order"

LEFT JOIN "Transfer Order Items" ON "Transfer Order" . "Transfer Order ID" = "Transfer Order Items" . "Transfer Order ID"  

WHERE 

                "Status"    not in ( 'draft'  )

GROUP BY    1,

 2,

 3,

 4,

  6

UNION ALL                                

/*stock in hand from Transfer Order Item*/

    SELECT

'Out',

"Date",

"Warehouse ID",

 "Product ID",

  sum ( "Transferred Quantity"),

 "Cost Price"

FROM     "Transfer Order"

LEFT JOIN "Transfer Order Items" ON "Transfer Order" . "Transfer Order ID" = "Transfer Order Items". "Transfer Order ID"   

WHERE   "Status"    not in ( 'draft'  )

GROUP BY   1,

 2,

 3,

 4,

 6

) AS   T1



Query Table - Inventory Age Tier

The below query tracks the age of each inventory and bucket them into the following groups.

  • 0-5 days old
  • 6-10 days old
  • 11-15 days old
  • 16-20 days old
  • older than 20 days

 

  SELECT

  "In/Out",

 "Product ID",

 "Quantity",

"Quantity On Hand",

 "Reverse Cumulative Total",

"Transaction Date",

 "Warehouse ID",

 "Purchase Price",

  /*identify the Transaction Date by comparing  Reverse Cumulative Total and Quantity On Hand */

  if(min(if( "Reverse Cumulative Total" - "Quantity On Hand"  >= 0 , "Reverse Cumulative Total" -"Quantity On Hand " , 99999999999 )) OVER ( PARTITION    BY     "Product ID" , "Warehouse ID"   ORDER BY "Transaction Date" DESC ) != 99999999999 , ( "Quantity On Hand" -("Reverse Cumulative Total" -"Quantity" )), "Quantity" ) "Final Qty" ,

if(min(if(
"Reverse Cumulative Total" - "Quantity On Hand"   >= 0 , "Reverse Cumulative Total" -"Quantity On Hand" , 99999999999 )) OVER ( PARTITION BY
"Product ID" , "Warehouse ID" ORDER BY
"Transaction Date"
DESC )   != 99999999999 , ( "Quantity On Hand" -("Reverse Cumulative Total" -"Quantity" )) * "Purchase Price" , "Quantity" * "Purchase Price" ) 'Inventory valuation',

  /*classify into different age tier*/

if ( Datediff ( current_date(), "Transaction Date" )  >= 0

AND 

                Datediff ( current_date() , "Transaction Date" )  <= 5 , '0-5 days', if( Datediff ( current_date() , "Transaction Date" )  >= 6

  AND 

                Datediff ( current_date() , "Transaction Date" )  <= 10 , ' 6-10 days', if ( Datediff ( current_date() , "Transaction Date" )  >= 11

  AND 

                Datediff ( current_date() , "Transaction Date" )  <= 15 , ' 11- 15 days', if( Datediff ( current_date() , "Transaction Date" )  >= 16

  AND 

                Datediff (current_date(), "Transaction Date" )  <= 21 , '16-20 days', 'greater than 20 days')))) 'Age tier'

FROM    "Stock In Hand"

WHERE "In/Out"   = 'In'

  AND 

                  "Quantity On Hand" - ("Reverse Cumulative Total" -"Quantity" )   > 0



Join Data using Lookup Column

Join the Inventory Aging Query table with the Warehouse and Items tables using the following lookup columns.     
  • Warehouse ID from Inventory Age Tier  query table - Warehouse ID from Warehouse table
  • Items ID from Inventory Age Tier  query table - Items ID   from Items table

Pivot View - Inventory Aging Report

Now you can create the inventory aging pivot over the Inventory Age Tier query table.

Follow the below steps to do so.
  1. Create a new pivot over the Inventory Age Tier query table.
  2. Drop the columns as follows.
    • Column - Age tier from Inventory Age Tier query table with Actual.
    • Row - Warehouse Name from Warehouse table and  Item Name from Items table with Actual.
    • Data - Purchased Quantity from Inventory Age Tier query table with Sum.



  3. The Click Here to Generate Pivot button allows you to generate the report.
  4. Hide the totals by clicking Show/Hide for all Totals.



  5. Click Sort > Custom Sort for Age Tier column to arrange the age tier.



  6. Now apply the required Theme. 



  7. Your final Inventory aging report is ready.


You can explore the solution by copying the workspace from the below link.

https://analytics.zoho.com/workspace/19601000018963001



    • 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

    • Need help getting my mail on iPhone and Tablet

      I need to access my Zoho mail via the iPhone Mail app. I have entered the login name, password and the incoming and outgoing servers, which my Mail Settings page says are imappro.zoho.com and smtppro.zoho.com. The iPhone keeps saying it cannot authenticate.

    • Cliq iOS can't see shared screen

      Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards

    • Add an background image to an email template in CRM

      Hi all, We wants to put an background image behind all our email templates. Is there a way to import this thru html. If i put the option background image in <body style="background-image:...</body> and i look to the preview it shows our background, but

    • Is there a way to show contact emails in the Account?

      I know I can see the emails I have sent and received on a Contact detail view, but I want to be able to see all the emails that have been sent and received between all an Accounts Contacts on the Account Detail view. That way when I see the Account detail

    • How do I bulk archive my projects in ZOHO projects

      Hi, I want to archive 50 Projects in one go. Can you please help me out , How can I do this? Thanks kapil

    • Copy contents of File Upload Field into Workdrive

      Hello, I have set up our CRM so that a Workdrive folder is automatically created for each Deal via workflow, this adds the id of the folder into a dedicated field. We also have a field on each Deal called 'Approved Layout', which is a file upload field.

    • Kaizen #198: Using Client Script for Custom Validation in Blueprint

      Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Deleting a memorized email address

      How can I delete a memorized email address? Even though the address has been deleted from Contacts, Zoho mail still auto suggests the address when typing it into the TO field. Thanks!

    • Multiple Cover Letters

      We are using the staffing firm edition of Recruit and we have noticed that candidates cannot add more than one cover letter. This is a problem as they might be applying for multiple jobs on our career site and when we submit their application to a client,

    • Introducing Record Category in CRM: Group options to see record status at a glance.

      Release update: Currently available for CN, JP, and AU DCs (all paid editions). It will be made available to other DCs by mid-March. Hello everyone, We are pleased to introduce Record Category in Zoho CRM - a new capability where the user can get an overview

    • 553 Relaying disallowed. Invalid Domain - gzkcompany.ro

      Hi there, Can you please assist me in getting the right domain settings? I just renewed my domain subscription, after expired and i got error: 553 Relaying disallowed. Invalid Domain - gzkcompany.ro Zoho mail can receive emails, but its impossible to

    • Not able to receive emails for a while

      I am not able to receive emails for a while now.

    • Confirmation requested: eligibility and process to downgrade to Forever Free — tenant bigbanghawking.com

      Thank you for your reply. I am testing Zoho Mail from Brazil with the tenant bigbanghawking.com (endpoint: mail.zoho.com) and we are currently on the Premium trial that expires 21/01/2026. Before deciding whether to pay or cancel, I need written confirmation

    • Create Tasklist with Tasklist Template using API v3

      In the old API, we could mention the parameter 'task_template_id' when creating a tasklist via API to apply a tasklist template: https://www.zoho.com/projects/help/rest-api/tasklists-api.html#create-tasklist In API v3 there does not seem to be a way to

    • Zoho API v2.0 - get ALL users from ALL projects

      Hello,        I've been trying to work on an automatization project lately and I find it difficult to work with this strict structure. To be more explicit, if i would like to get all users participating in a project i would need to get all projects first.       Same thing with projects. If i want to get all projects, I would need to get all portals first.        The problem with this aproach is that it consumes a lot of time and resources.             I want to ask if there is another way of getting

    • [Webinar] Solving business challenges by transitioning to Zoho Writer from legacy tools

      Moving to Zoho Writer is a great way to consolidate your business tools and become more agile. With multiple accessibility modes, no-code automation, and extensive integration with business apps and content platforms, Zoho Writer helps solve your organization's

    • الموقع لا يقوم بالسداد

      السلام عليكم ورحمة الله وبركاته وبعد من أمس وانا احاول السداد للدومين YELLOWLIGHT ولا اتمكن من السداد اقوم بتعبئة جميع البيانات ولكن دون جدوى يطلع لى حدث خطأ ما

    • New in Office Integrator: Enhanced document navigation with captions and cross references

      Hi users, We're pleased to introduce captions, table of tables and figures, and cross-references in the document editor in Zoho Office Integrator. This allows you to structure documents efficiently and simplify document navigation for your readers from

    • Where Do I set 24h time format in Cliq?

      Where Do I set 24h time format? Thanks

    • 🎉 ¡Seguimos trayendo novedades a Español Zoho Community! 🎉 Confirmada la agenda y ubicación para los Workshops Certificados

      Si todavía no te has hecho con tu entrada para nuestros Workshops Certificados del próximo 26 y 27 de marzo o, por el contrario, estabas esperando que confirmáramos dónde los celebraremos, ¡este post es para ti! 📍¿Dónde nos vemos?📍 Nuestros Workshops

    • User is already present in another account error in assigning users to marketing automation

      Hello everyone Greeting, I had a problem in assigning user in marketing automation, when I try to add it I see this error: (User is already present in another account error) what should I do?

    • How do I get complete email addresses to show?

      I opened a free personal Zoho email account and am concerned that when I enter an email address in the "To", "CC", fields, it changes to a simple first name. This might work well for most people however I do need to see the actual email addresses showing

    • What's New in Zoho POS - January 2026

      Hello everyone, Welcome to Zoho POS’s monthly updates, where we share our latest feature updates, enhancements, events, and more. Let’s take a look at how January went. Sort and resolve conflicts Conflicts are issues that may arise when registers and

    • Removing Tables from HTML Inventory Templates - headers, footers and page number tags only?

      I'm a bit confused by the update that is coming to HTML Inventory Templates https://help.zoho.com/portal/en/kb/crm-nextgen/customize-crm-account/customizing-templates/articles/nextgen-update-your-html-inventory-templates-for-pdf-generator-upgrade It says

    • Outlook is blocking incoming mail

      Outlook is blocking all emails sent from the Zoho server. ERROR CODE :550 - 5.7.1 Unfortunately, messages from [136.143.169.51] weren't sent. Please contact your Internet service provider since part of their network is on our block list (S3150). It looks

    • Not receiving email from customers and suppliers

      I am getting error . most of the customers tell me not able to send me email please check i have attached screenshot

    • Create user

      Hello I want to create user, but i get this error Unusual activity detected from this IP. Please try again after some time.

    • File emails in Shared email folder

      Hi, I am unable to allow users to collaborate in Shared email folders: User 1 shares a folder let's say "SharedTopic" with full permissions Users 2 and 3 can see this folder but are unable to add emails to this folder or search in this folder. For example,

    • Consolidated report for multi-organisation

      I'm hoping to see this feature to be available but couldn't locate in anywhere in the trial version. Is this supported? The main aim to go to ERP is to have visibility of the multi-organisation in once place. I'm hopeful for this.

    • Integrating Zoho Suite and apps more with Linux

      I just got introduced with Zoho just couple of months ago, and I've already planned to contribute to it, even though it's not an open-source software. Still I have found it's potential to beat the tech giants and still being respective towards data privacy

    • How to Switch from Outlook for Mac to Outlook for Windows

      The most often used file formats for users to manage crucial data are OLM and PST files. PST files keep a copy of data on the configured system from Outlook, while the OLM file contains the Mac Outlook data items, which are only accessible with Outlook

    • Zoho CRM for Everyone's NextGen UI Gets an Upgrade

      Hello Everyone We've made improvements to Zoho CRM for Everyone's Nextgen UI. These changes are the result of valuable feedback from you where we’ve focused on improving usability, providing wider screen space, and making navigation smoother so everything

    • Name autocomplete

      Hi, During searching emails the web tool does not always propose the auto-completion of the saved emails. As a result I either have to go to contacts and look up the exact email, or the exact full name including the middle name and any dots, which is

    • Are custom portals accessible on the Zoho learn smartphone app?

      In other words, can users external to my organisation, once signed up, use the app in the same way as internal users? Thanks

    • How to increase my Zoho sign limit.

      I cannot send a document/contract for signature. Zoho sign says I reached my monthly limit. May I know how to fix this please? Thanks! 

    • Can not add m365 outlook account to zohomail.

      I am attempting to use zoho mail as an imap client to add my outlook.com m365 account. In the m365 exchange admin center i have made sure the imap is enabled. In zoho mail i go to settings, mail accounts, add account, add imap account, i select "outlook",

    • Unable to attach Work Order / Service Appointment PDF to Email Notifications (Zoho FSM)

      I’m trying to include the Work Order PDF or Service Appointment PDF as an attachment in Email Notifications (automation/notification templates), but I don’t see any option to attach these generated PDFs. Is this currently supported in Zoho FSM? If not,

    • local file csv import problem

      The issue occurs when I upload a CSV file via Databridge. In the preview, everything looks correct — the values are in the proper columns. However, after clicking Import, the first column becomes empty, and the values from that column appear in a new

    • Función Deshacer y Rehacer

      Hola. Soy un reciente usuario de Zoho Notebook que he migrado desde Evernote. He encontrado en falta una función que considero muy importante: un botón para "deshacer". Es frustrante cuando se borra un parte del texto o un archivo de una nota, generalmente

    • Tip #59- Technician Console: Exploring View option- 'Insider Insights'

      Hello Zoho Assist Community! Ever wondered how technicians adapt quickly during a live support session? Imagine a customer reaching out with an issue that’s disrupting their work. The technician starts a remote session and begins troubleshooting right

    • Next Page