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

    • How to compare a subform lookup field that allows multiple entries when edited

      I have a form with a subform with multiple fields. One of the fields is a lookup field that allows a multi select. On edit validation, I want a workflow to execute only when the entries in that subform field has changed. The old. function is not working

    • Translation of Tooltip Messages

      The descriptive help messages should be available to provide translations for.

    • Business Day Logic Update: More Accurate Scheduling for Your Workflows

      Hello everyone, We’re improving how business-day calculations work in workflows, especially when triggers happen on weekends. This update ensures that offsets like +0, +1, and +2 business days behave exactly as intended, giving you clearer and more predictable

    • Lead Blueprint transition in custom list view

      Hi, Is It possible to insert the Blueprint transition label in a custom Canvas list view? I am using Lead module. I see the status, but it would be great if our users could execute the Blueprint right from the list view without having to enter the detailed

    • Email Notifications not pushing through

      Hi, Notifications from CRM are not reaching my users as they trigger.  We have several workflow triggers set up that send emails to staff as well as the notifications users get when a task is created for them or a user is tagged in the notes.  For the past 6 days these haven't been coming through in real time, instead users are receiving 30-40 notifications in one push several hours later.  This is beginning to impact our daily usage of CRM and is having a negative effect on our productivity because

    • Debit opening balances of vendors

      Dear colleagues: I am looking at the trial balance as on 31st March 2024, and punching opening balances (1st April 2024) in Zoho Books. Vendors have credit balances, by its nature, but some of our vendors have debit balances as well (e.g., we have paid

    • Is there an equivalent to the radius search in RECRUIT available in the CRM

      We have a need to find all Leads and/or Contacts within a given radius of a given location (most likely postcode) but also possibly an address. I was wondering whether anyone has found a way to achieve this in the CRM much as the radius search in RECRUIT

    • Allow Text within a Formula

      Hi, I would like to be able to use this for others things like taking an existing Date Field and copying its value, so by entering getDay(Date)&"-"&getMonth(Date)&"-"&getYear(Date) it results in 01-02-2026. And then when the Date is changed so is this

    • Change Number Field to Decimal Field

      Hi, It would be nice to be able to change the field type without having to delete it and create a new one, messing up the database and history. Thanks Dan

    • Pipeline.Company Name field shows up as numbers! [Bigin Developer Console > Component > URL]

      Hi there, I am setting up to invoke URL to send infromation zoho bigin > zoho forms with company name pre-fill in the form. however when I use : ${Pipelines.Company Name} field it shows up as a string of number instead of words. Help.

    • Add specific field value to URL

      Hi Everyone. I have the following code which is set to run from a subform when the user selects a value from a lookup field "Plant_Key" the URL opens a report but i want the report to be filtered on the matching field/value. so in the report there is

    • How to Move Behavior, Acquisition, Polls & Forms Data from Zoho PageSense to Zoho Analytics?

      Hi Zoho Community, I'm looking for a way to transfer data from Zoho PageSense to Zoho Analytics, specifically: Behavioral data (clicks, scrolls, heatmaps, etc.) Acquisition data (traffic sources, campaigns, etc.) Polls and forms data As far as I can tell:

    • Zoho Social - Feature Request - Non-US Date Format

      Hi Social Team, I have noticed that there is no option to change the date format from US mm/dd/yyyy to others like dd/mm/yyyy. It would be great to see this added as the platform matures. Thanks for considering this feedback.

    • In arattai received message can't be deleted

      The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a received

    • Zoho CRM Community Digest - December 2025 | Part 1

      Hello Everyone! In the first half of December, Zoho CRM rolled out auto-invite for portals, smarter CPQ with Zia suggestions and price rules, and Query Workbench for faster query building. We also highlight other noteworthy conversations you shouldn’t

    • Zoho Flow Credits

      Hi , I would like to ask the reason why every time I added plus credit but few days later I will return back to default? (as below I add credit to 3000 but today It change back to 1000) Most important is, when the credit fully used, not any reminder to

    • Custom Module Send Email not filling To automatically after adding Email field also

      I create custom module in zohoCRM but problem is in custom module when i click send email there in popup To is filling automatic its coming empty which i need to manually add an email there. How can I fix it? I already added the Email field there, but

    • Introducing Intake Forms

      We are excited to announce the release of Intake Forms, a new feature in Zoho Contracts designed to make contract requests simple, structured, and efficient. Intake Forms allow organization members to request new contracts through a web form instead of

    • Manage control over Microsoft Office 365 integrations with profile-based sync permissions

      Greetings all, Previously, all users in Zoho CRM had access to enable Microsoft integrations (Calendar, Contacts, and Tasks) in their accounts, regardless of their profile type. Users with administrator profiles can now manage profile-based permissions

    • eIDAS 2.0: What's changed for digital trust in Europe, and where Zoho Sign stands

      Hi there! It's that time of year when many of us get our ducks in a row. A new year often makes us reassess priorities, and for businesses in the EU, it means taking a closer look at how digital identities and electronic signatures work across borders,

    • Directly Edit, Filter, and Sort Subforms on the Details Page

      Hello everyone, As you know, subforms allow you to associate multiple line items with a single record, greatly enhancing your data organization. For example, a sales order subform neatly lists all products, their quantities, amounts, and other relevant

    • Add Israel & Jewish Holidays to Zoho People Holidays Gallery

      Greetings, We hope you are doing well. This feature request is related to Zoho People - please don't move it to zoho one! We are writing to request an enhancement to the Holidays Gallery in Zoho People. Currently, there are several holidays available,

    • Any update on much requested feature, to delete attachments without deleting the e-mail body?

      People have been requesting the ability to delete e-mail attachments without deleting the e-mail for more than ten years now. The latest I see is marked "Working On It" and a year ago it was supposedly being added, see here: https://help.zoho.com/portal/en/community/topic/is-there-a-way-to-delete-mail-attachments-without-deleting-the-text

    • How to delete attachments form Zoho mail accounts

      I can't find a way to delete attachments from Zoho mail messages, either individually or in bulk. Searches here are providing conflicting results and often talk about workspace, whereas I am only interested in how to delete attachments that are seen with

    • Unable to fetch ticket by custom field value

      I'm trying to set up a Flow to fetch a ticket based on a custom field value. This seems like it should be pretty straightforward, but it's not working for me. I keep getting an error saying Zoho Desk says "Extra query parameter 'cf_creator_record_id'

    • Zoho Inventory - Composite Items - Assembly - Single Line Item Quantity of One

      Hi Zoho Inventory Team, Please consider relaxing the system rules which prevent an assembly items from consisting of a single line item and outputting a quantity of 1. A client I'm currently working with sells cosmetics and offers testers of their products

    • What's New in Zoho Inventory | Q2 2025

      Hello Customers, The second quarter have been exciting months for Zoho Inventory! We’ve introduced impactful new features and enhancements to help you manage inventory operations with even greater precision and control. While we have many more exciting

    • Zoho POS App Hanging Issue – Sales Becoming Difficult

      The Zoho POS app frequently hangs and becomes unresponsive during billing, making it very difficult to complete sales smoothly. This commonly happens while adding items, during checkout, or at payment time, especially during peak hours. These issues cause

    • Writing by Hand in "Write" Notes

      Hi there! I just downloaded this app a few moments ago, and I was wondering if there was a way to write things by hand in "Write" mode instead of just typing in the keyboard. It would make things a bit more efficient for me in this moment. Thanks!

    • Zoho Mail Verification Following DNS Switch from Cloudflare to Hosting Provider

      I initially configured my domain’s ( https://roblozapk.com/ ) email with Zoho Mail while using Cloudflare to manage my DNS records such as MX, SPF, and DKIM. At that time, all email services were working correctly without any issues. Recently, I removed

    • "Spreadsheet Mode" for Fast Bulk Edits

      One of the challenges with using Zoho Inventory is when bulk edits need to be done via the UI, and each value that needs to be changed is different. A very common use case here is price changes. Often, a price increase will need to be implemented, and

    • SAML in Zoho One vs Zoho Accounts

      What is the difference between setting up SAML in Zoho Accounts: https://help.zoho.com/portal/en/kb/accounts/manage-your-organization/saml/articles/configure-saml-in-zoho-accounts ... vs SAML in Zoho One?: https://help.zoho.com/portal/en/kb/one/admin-guide/custom-authentication/setting-up-custom-authentication-with-popular-idps/articles/zohoone-customauthentication-azure

    • Deactivated Zoho One account can sign in

      I am concerned by the fact that deactivated users in Zoho One have the ability to sign in even after their account has been deactivated (not deleted). these inactive identities have no access to individual Zoho apps or data. based on my experience they

    • How can I reset the password for a user in Zoho Projects

      We need to reset the password for a user in Zoho Projects. I am the admin portal owner and there was nothing to be found to do this. very confusing.

    • How can I add a comment to an existing ticket via API?

      I need to add comments/notes to the history of an existing ticket using the API without overwriting the original ticket description. Thanks!

    • Marketer's Space: Proven tips to improve open rates – Part III

      Hello Marketers! Welcome back to another post in Marketer's Space! This is the final post in the "open rate series". In the first and second parts, we discussed topics ranging from sender domains to pre-headers—but we're not done yet. A few more important

    • Attention Deluge Developers: Important Update Regarding "Send Email Deluge Task"

      Hi Deluge Users, We hope this message finds you well. We would like to inform you about a recent update on the Send emails deluge task. If you are using this deluge task (SalesIQ Scripts) for your Zobot, widgets, or form controllers to send emails, please

    • Zoho Social - Feature Request - Reviewer Role

      Hi Social Team, I've come across this with a couple of clients, where they need a role which can review and comment on posts but who has no access to create content. This is a kind of reviewer role. They just need to be able to see what content is scheduled

    • Zoho Books/Inventory - Update Marketplace Sales Order via API

      Hi everyone, Does anyone know if there is a way to update Sales Orders created from a marketplace intigration (Shopify in this case) via API? I'm trying to cover a scenario where an order is changed on the Shopify end and the changes must be reflected

    • Zoho Inventory / Finance Suite - Add feature to prevent duplicate values in Item Unit field

      I've noticed that a client has 2 values the same in the Unit field on edit/create Items. This surprised me as why would you have 2 units with the same name. Please consider adding a feature which prevents this as it seems to serve no purpose.

    • Next Page