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 can i download and secure all my mails from the archive folders?

      Hi, i would like to download all my mails from my archive folders and secure it on my external HDD. Is this possible? Thx. amir

    • How to open filtered report in popup using Zoho Creator Deluge?

      First report: There is so many records in Report, If I click one record, pop up is occur. Second report (Pop up): there is also so many record data, and this pop up is also Report, not Form. First report: It has got "Sales Order" field. when I click any

    • Make panel configuration interface wider

      Hi there, The same way you changed the custom function editor's interface wider, it would be nice to be able to edit panels in pages using the full width of the screen rather than the currently max-width: 1368px. Is there a reason for having the configuration panel not taking the full width? Its impossible at this width to edit panels that have a lot of elements. Please change it to 100% so we can better edit the layouts. Thanks! B.

    • Can you default reports/charts to report the current week?

      Our data table maintains two years of data. Management wants certain report to automatically filter the report to the latest calendar week. I know I can do this manually with filters but I want the report to automatically default to the latest calendar

    • Rendering PDF to view on page

      My company upload lots of PDF files onto Zoho. But every time we open it, it downloads the file instead of viewing it on the web page. Does Zoho allow uploaded PDF files to be rendered to view on web page yet? I've been trying to use <embed> or <object> but it cannot be loaded.  (similar thread: https://help.zoho.com/portal/community/topic/how-to-open-a-pdf-file-of-a-view-in-preview-mode)

    • Overlapping Reports in Dashboards

      It's rare, but occasionally it would be a good feature if I were able to overlap reports, either fully or partially in the Dashboards. Also, then having the ability to move objects to the front or rear, or make them transparent/translucent would be good

    • Feature request - pin or flag note

      Hi, It would be great if you could either pin or flag one or more notes so that they remain visible when there are a bunch of notes and some get hidden in the list. Sometimes you are looking for a particular name that gets lost in a bunch of less important

    • Admin guide: Handling Mailbox Settings for better user management

      Managing day-to-day email scenarios, such as supporting users with multiple email addresses, ensuring uninterrupted email access during employee absences, enabling secure mailbox sharing, and enforcing organizational security and compliance, can be challenging

    • Cisco Webex Calling Intergration

      Hi Guys, Our organisation is looking at a move from Salesforce to Zoho. We have found there is no support for Cisco Webex Calling however? Is there a way to enable this or are there any apps which can provide this? Thanks!

    • Designing a practical Zoho setup for a small business: lessons from a real implementation

      I recently finished setting up a Zoho-based operating system for a small but growing consumer beauty business (GlowAtHomeBeauty), and I wanted to share a practical takeaway for other founders and implementers. The business wasn’t failing because of lack

    • DKIM (Marketing emails) UNVERIFIED (Zoho One)

      I'm having a problem with Zoho One verifying my Marketing Email DKIM Record for MYFINISHERPHOTOS.COM. I have removed and re-entered the ownership, DKIM (Transactional emails), SPF and Marketing DKIM and all of them come back verified except the DKIM (Marketing

    • Zoho Recruit Community Meet-up - India

      Namaste, India. 🙏🏼 The Zoho Recruit team is hitting the road—and we 're absolutely excited behind the scenes. Join us for the Zoho Recruit India Meet-up 2026, a morning designed to make your recruiting life easier (and a lot more effective). Date City

    • Generate a Zoho Sign link

      From time to time I get a response "I never received your you e-document for electronic signature" is there a way to generate a Zoho Sign link to share.

    • Zoho Social - Cliq Integration / Bot

      Dear community / zoho, I am looking for a way to create a bot within Zoho Cliq to update my colleagues about our Zoho Social activities. For example, if a new post is published, it would be great if this post automatically would be shared in our social

    • CRM gets location smart with the all new Map View: visualize records, locate records within any radius, and more

      Hello all, We've introduced a new way to work with location data in Zoho CRM: the Map View. Instead of scrolling through endless lists, your records now appear as pins on a map. Built on top of the all-new address field and powered by Mappls (MapMyIndia),

    • Is it possible to create a word cloud chart in ZoHo Analystics?

      Hi there, I have a volume of transaction text that I would like to analyse using word cloud (or other approcah to detect and present word frequency in a dataset). For example, I have 50,000 records describing menu items in restaurants. I want to be able

    • How to Fix the Corrupted Outlook 2019 .pst file on Windows safely?

      There are multiple reasons to get corrupted PST files (due to a power failure, system crash, or forced shutdown) and several other reasons. If You are using this ScanePST.EXE Microsoft inbuilt recovery tool, it only supports the minor corruption issue

    • [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

    • How to drag row(s) or column(s)?

      Hi. Selecting a row or column and then dragging it to a new position does not seem to work. Am i missing something or this is just not possible in Zoho Sheet? Cheers, Jay

    • Building Toppings #5 - Creating and configuring custom service connections in Bigin Toppings

      Hello Biginners, Integrating Bigin with external applications extends its capabilities and enables customized functionalities. In our last post, we saw how to create a default service connection. Today, we'll see how to create a custom service connection

    • Admin asked me for Backend Details when I wanted to verify my ZeptoMail Account

      Please provide the backend details where you will be adding the SMTP/API information of ZeptoMail Who knows what this means?

    • Optimising CRM-Projects workflows to manage requests, using Forms as an intermediary

      Is it possible to create a workflow between three apps with traceability between them all? We send information from Zoho CRM Deals over to Zoho Projects for project management and execution. We have used a lookup of sorts to create tasks in the past,

    • Conditional fields when converting a Lead and creating a Deal

      Hi, On my Deal page I have a field which has a rule against it. Depending on the value entered, depends on which further fields are displayed. When I convert a Lead and select for a Deal to be created as well, all fields are shown, regardless of the value

    • ATE Session on Payment Gateways: Our experts are live now. Post your questions now!

      Hello everyone, Our experts are all excited to answer all your questions related to payment workflows. Please feel free to join this session and learn more about this topic. If you have a query at anytime, please post them here.

    • Upload data deleted all Zoho form data that we manage

      Good morning. Let me introduce myself, I'm Iky from Indonesia. I'm experiencing an error or problem using Zoho Forms. I manage Zoho Forms, but I previously encountered an error when I misclicked the delete button in the upload format. It apparently deleted

    • ZOHO FORMにURL表示ができない

      初心者です。 ZOHO FORM で宿泊者名簿を作っています。 ゲストが、URLをクリックするとStripeで支払いができるようにURLを表示をしたいのですが、 上手くできません。 やり方が分かる方、ぜひ教えてください。

    • Custom module - change from autonumber to name

      I fear I know the answer to this already, but thought I'd ask the question. I created a custom module and instead of having a name as being the primary field, I changed it to an auto-number. I didn't realise that all searches would only show this reference.

    • No Automatic Spacing on the Notebook App?

      When I'm adding to notes on the app, I have to add spaces between words myself, rather than it automatically doing it. All my other apps add spacing, so it must be something with Zoho. Is there a setting I need to change, or something else I can do so

    • Holidays - Cannot Enter Two Holidays on Same Day

      I have a fairly common setup, where part-time employees receive 1/2 day's pay on a holiday and full-time employees receive a full day's pay. Historically, I've been able to accommodate this by entering two separate holidays, one that covers full-time

    • Zoho Bookings and Survey Integration through Flow

      I am trying to set up flows where once an appointment is marked as completed in Zoho Bookings, the applicable survey form would be sent to the customer. Problem is, I cannot customise flows wherein if Consultation A is completed, Survey Form A would be

    • Campaigns set up and execution assistance

      Hello Community, Can someone recommend a professional who can assist with the completion of my set up and deployment of Campaigns? Looking for a person or company that is not going to ask for big dollars up-front without a guarantee of performance to

    • Card Location in Zobot

      Hello, when using the “Location” card in a codeless builder Zobot, the behavior in WhatsApp is inconsistent. When asking the user to share their location, they can type a message, which will return the message “Sorry, the entered location is invalid.

    • Zobot with Plugs

      Hello, I am having a problem with Zobot using Plugs. Here is my current flow: When I run the flow, I should immediately see the messages from the initial cards (Send Message cards), then after running the plug, and finally, see the messages after the

    • Kaizen #223 - File Manager in CRM Widget Using ZRC Methods

      Hello, CRM Wizards! Here is what we are improving this week with Kaizen. we will explore the new ZRC (Zoho Request Client) introduced in Widget SDK v1.5, and learn how to use it to build a Related List Widget that integrates with Zoho WorkDrive. It helps

    • Remove Powered by Zoho at the footer

      Hi, I've read two past tickets regarding this but it seems that the instructions given are outdated. I assume the layout keeps on changing, which makes it frustrating for me to search high and low. Please let me know how exactly do I do this now? Th

    • Error AS101 when adding new email alias

      Hi, I am trying to add apple@(mydomain).com The error AS101 is shown while I try to add the alias.

    • No Need To Fix Something That Is Working

      Zoho Books is a great financial tool which helps businesses to become more efficient and productive with day-to-day operations. As such, every change, upgrade, improvement needs to be carefully thought before implemented in the software and I'm sure Zoho

    • Creating a task, i can not work out how to get ID for What_Id

      hi From Module A function I map Module B record reference membershipid (ours). I need Module B Zoho ID to create the related to on task. All examples i've seen start with the Zoho ID. void automation.LTM_Assign_Dispute_Task(String membershipid) { try

    • Using email "importance" as workflow-criteria

      I'd like to set up a workflow that triggers if an incoming email has been flagged as "high importance" but I'm not seeing any way to do that. Hopefully I'm just missing something obvious...?

    • This domain is not allowed to add. Please contact support-as@zohocorp.com for further details

      I am trying to setup the free version of Zoho Mail. When I tried to add my domain, theselfreunion.com I got the error message that is the subject of this Topic. I've read your other community forum topics, and this is NOT a free domain. So what is the

    • Next Page