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

    • Recruit API search

      Hi all, Attempting to call the search api endpoint from Postman using the word element as mentioned in api docs Search Records - APIs | Online Help - Zoho Recruit When making the call to /v2/Candidates/search?word=Saudi receive response of { "code": "MANDATORY_NOT_FOUND",

    • Saving reading position + Keep screen on

      While Zoho Notebook is excellent for saving and annotating articles, its utility is severely limited by the lack of reading progress synchronization. On the Android app, if a user exits a long note after reading 50%, the app fails to save the position.

    • Multiple Vendor SKUs

      One of the big concerns we have with ZOHO Inventory is lack of Vendor Skus like many other inventory software packages offer. Being able to have multiple vendor skus for the same product would be HUGE! It would populate the appropriate vendor Sku for

    • Zoho LandingPage is integrated with Zoho One!

      Greetings to the Zoho One users out there! We're delighted to let you know that Zoho LandingPage is available in Zoho One too! With Zoho LandingPage, you can host custom-made landing pages, and persuade the visitors to dive deeper by making further clicks,

    • Android app sync problem - multiple devices have same problem

      Hello, I am having a problem with synchronization in the Android app. When I create a drawing, the data does not sync correctly—only a blank note is created without the drawing. I tested this on multiple devices, including phones and tablets, and the

    • How can i resend a campaign to only one of the recipients on the original campaign

      How can i resend a campaign to only one of the recipients on the original campaign ? Sincererly, Mike

    • How to show branch instead of org name on invoice template?

      Not sure why invoices are showing the org name not the branch name? I can insert the branch name using the ${ORGANIZATION.BRANCHNAME} placeholder, but then it isn't bold text anymore. Any other ideas?

    • Create CRM Deal from Books Quote and Auto Update Deal Stage

      I want to set up an automation where, whenever a Quote is created in Zoho Books, a Deal is automatically created in Zoho CRM with the Quote amount, customer details, and some custom fields from Zoho Books. Additionally, when the Sales Order is converted

    • Marketing Automation Requirements Questions

      I would like to set up a multi-email drip campaign- please see the structure below and confirm if I can achieve this set up in Zoho marketing automation. Where applicable, highlight gaps and workarounds. Thanks Drip email campaign- Can I create one drip

    • Zoho CRM for Gmail Extension Not Working in Brave Browser?

      Is anyone able to get the Zoho CRM Chrome Extension working in the Brave browser? They're both built on the Chromium platform and every other Chrome Extension works with the exception of Zoho CRM for Gmail so any ideas here?

    • Sharing URLs and direct access

      Hello, I am storing my team's email signature images on Workdrive. I am creating a public image download share and adding “?directDownload=true” so that the image can be accessed without the Workdrive interface. A few questions: 1) Can we generate friendly

    • how to change the page signers see after signing a document in zoho sign

      Hello, How can I please change the page a signer sees after signing a document in Zoho Sign? I cannot seem to find it. As it is now, it shows a default landing page "return to Zoho Sign Home". Thanks!

    • Question about using custom_fields in Storefront Add-to-Cart API (error 2003 – required details)

      Hi everyone, I’m working with the Zoho Commerce Storefront API, specifically the Add to Cart endpoint: POST /storefront/api/v1/cart According to the documentation, this endpoint supports a custom_fields parameter for adding line-item custom data. I’m

    • Can a project be cloned?

      Good afternoon, greetings. I would like to ask if it's possible to clone a project in Microsoft Project. I found a way to do it using templates, but I'm not sure if there's a direct way to clone a project. Thank you in advance for your attention, and

    • Timesheet Tasks in Zoho Books: associate to service item

      How do we associate a service item to timesheet tasks in Zoho Books? For example: Joe spent 5 hours on project:task1 which is Service Item#1 (Income:Service1). When the invoice is issued thru the Project Invoice section, this is not available. When the

    • Why Sharing Rules do Not support relative date comparison???

      I am creating a Sharing Rule and simply want to share where "Last Day of Coverage" (Date field) is Greater than TODAY (Starting Tomorrow). However, sharing rules don't have the option to compare a date field to a relative date (like today), only to Static

    • Task/Activity indicator in SalesPipeline overview has disappeared

      I Just logged in my ZOHO CRM first 2026 checking my salespipeline overview , Every record card used to show an indication that there was an open task (Yellow if the expiry date was close, red if the expiry date was today and grey when it had expired).

    • Tip #56- Accessibility Controls in Zoho Assist: Hearing- 'Insider Insights'

      As we begin the new year, it’s a great time to focus on making our tools more inclusive and accessible for everyone. Remote support often involves long hours in front of screens, varying lighting conditions, and users with diverse accessibility needs.

    • Zoho Desk Android app update: Table view for All Departments view, custom button

      Hello everyone! In the latest version(v2.9.25) of the Zoho Desk Android app update, we have introduced Table view for the 'All Departments' view in the ticket module. We also have supported an option that allows tickets in the Table view to be sorted

    • What's New - December 2025 | Zoho Backstage

      In December, Backstage introduced a focused set of updates that improve how you manage registrations, communicate with attendees, and track participation. These enhancements are designed to give organizers greater flexibility and clearer control across

    • Need code format to specify default values

      Can someone please direct me to the code syntax or the proper translation per the instructions circled below. These instructions don't seem correct.

    • Problem with CRM Connection not Refreshing Token

      I've setup a connection with Zoom in the CRM. I'm using this connection to automate some registrations, so my team doesn't have to manually create them in both the CRM and Zoom. Connection works great in my function until the token expires. It does not refresh and I have to manually revoke the connection and connect it again. I've chatted with Zoho about this and after emailing me that it couldn't be done I asked for specifics on why and they responded. "The connection is CRM is not a feature to

    • Add multiple users to a task

      When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesman

    • A Roundup of Zoho Sprints 2025

    • Sorting Custom Date in API isn't working w pagination limit

      How can we sort a custom field with DATE using pagination? Starting at page=1 then moving to page=2 with a limit of 10 each, its all messed up and even shows some of the same records as page 1? https://www.zohoapis.com/crm/v2/INVOICE_MODULE/search?criteria=(FM_Contact_ID:equals:1234)&sort_by=Invoice_Date&sort_order=desc&per_page=10&page='

    • SAP Business One(B1) integration is now live in Zoho Flow

      We’re excited to share that SAP Business One (B1) is now available in Zoho Flow! This means you can now build workflows that connect SAP B1 with other apps and automate routine processes without relying on custom code. Note: SAP Business One integration

    • Enhancement in Role and Profile mapping of agents in Sandbox

      Hello everyone! We have brought in a modification in the way users are mapped to a particular role and profile in Sandbox. What has changed? When agents are copied from production to Sandbox: If a user's current role and profile is available in Sandbox,

    • The reason I switched away from Zoho Notebook

      My main reason for switching to Zoho was driven by three core principles: moving away from US-based products, keeping my data within India as much as possible, and supporting Indian companies. With that intent, I’ve been actively de-Googling my digital

    • Decimal places settings for exchange rates

      Hello, We are facing issues while matching vendor payments with banking feeds. As we often import products/services exchange rate comes into play. Currently, ZOHO allows only six digits for decimal places. We feel that conversions like JPY to INR require

    • Zoho removed ability to see all Scheduled Reports!

      If you are not the owner of a scheduled report, Zoho recently removed the capability to see each scheduled report. As an admin who relies on seeing all scheduled reports being sent, this is a terrible update. Now I cannot see ANY scheduled reports...even the ones I am being sent!!  This should be a setting for admins to control.  This is a bad update.

    • Automate Backups

      This is a feature request. Consider adding an auto backup feature. Where when you turn it on, it will auto backup on the 15-day schedule. For additional consideration, allow for the export of module data via API calls. Thank you for your consideration.

    • GCLID and Zoho Bookings

      Is there anyway to embed a Zoho Bookings signup on a landing page and pass the GCLID information? More specifically, can this be done using auto-tagging and not manual tagging the GCLID? I know Zappier has an integration to do this but is there a better

    • Merge Items

      Is there a work around for merging items? We currently have three names for one item, all have had a transaction associated so there is no deleting (just deactivating, which doesn't really help. It still appears so people are continuing to use it). I also can't assign inventory tracking to items used in past transactions, which I don't understand, this is an important feature moving forward.. It would be nice to merge into one item and be able to track inventory. Let me know if this is possible.

    • Create PO from an invoice

      We are a hardware and software sales company which receives orders over the internet. We drop ship most of our products from a warehouse outside of our company. Our orders get sync'd into Zoho from our store via onesaas as invoices. It would be great

    • Blueprint or Validation Rules for Invoices in Zoho Books

      Can I implement Blueprint or Validation Rules for Invoices in Zoho Books? Example, use case could be, Agent confirms from client that payment is done, but bank only syncs transactions tomorrow. in this case, Agent can update invoice status to done, and

    • Resetting auto-number on new year

      Hi everyone! We have an auto-number with prefix "D{YYYY}-", it generates numbers like D2025-1, D2025-2, etc... How can we have it auto-reset at the beginning of the next year, so that it goes to D2026-1? Thanks!

    • The Social Wall: December 2025

      Hello everyone! As we wrap up the final edition of the Social Wall for 2025, it’s the perfect time to look at what went live during December. QR code generator From paying for coffee to scanning metro tickets, QR codes are everywhere and have made everyday

    • Custom AI solutions with QuickML for Zoho CRM

      Hello everyone, Earlier, we introduced Custom AI Solutions in CRM that let you access QuickML for your custom AI needs. Building on that foundation, we’ve now enabled a deeper integration: QuickML models can be seamlessly integrated into CRM, and surface

    • Helper Functions and DRY principle

      Hello everyone, I believe Deluge should be able to use 'Helper functions' inside the main function. I know I can create different standalones, but this is not helpful and confusing. I don't want 10000 different standalones, and I dont want to have to

    • 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

    • Next Page