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



    Access your files securely from anywhere






                    Zoho Campaigns Resources




                          Zoho Developer Community



                              Bootcamp
                              Community
                              Pricing
                              Webinars
                              What's New
                              Integrations
                              Contact Support
                              Contact Sales
                                Zoho Survey Resources
                                  Features
                                  Survey Templates
                                  Integrations
                                  Community
                                  iOS Mobile Surveys
                                  Pricing
                                  Android Mobile Surveys
                                  Insurvey Blogs
                                  Insight Blogs
                                  Tips & Tricks





                                              Latest Feature Updates

                                                • Desk Community Learning Series


                                                • Digest


                                                • Functions


                                                • Meetups


                                                • Kbase


                                                • Resources


                                                • Glossary


                                                • Desk Marketplace


                                                • MVP Corner


                                                • Word of the Day


                                                • Ask the Experts




                                                    Zoho SalesIQ Resources

                                                          • 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


                                                          Manage your brands on social media
                                                          Access Zoho Social

                                                            Zoho Pagesense Resources


                                                                Zoho TeamInbox Resources

                                                                  Zoho DataPrep Resources


                                                                    Zoho CRM Plus Resources

                                                                      Zoho Books Resources


                                                                        Zoho Subscriptions Resources

                                                                          Zoho Projects Resources


                                                                            Zoho Sprints Resources


                                                                              Qntrl Resources


                                                                                Zoho Creator Resources



                                                                                    Zoho CRM Resources

                                                                                    • CRM Community Learning Series

                                                                                      CRM Community Learning Series


                                                                                    • Kaizen

                                                                                      Kaizen

                                                                                    • Functions

                                                                                      Functions

                                                                                    • Meetups

                                                                                      Meetups

                                                                                    • Kbase

                                                                                      Kbase

                                                                                    • Resources

                                                                                      Resources

                                                                                    • Digest

                                                                                      Digest

                                                                                    • CRM Marketplace

                                                                                      CRM Marketplace

                                                                                    • MVP Corner

                                                                                      MVP Corner







                                                                                        Design. Discuss. Deliver.

                                                                                        Create visually engaging stories with Zoho Show.

                                                                                        Get Started Now


                                                                                          Zoho Show Resources

                                                                                            Zoho Writer

                                                                                            Get Started. Write Away!

                                                                                            Writer is a powerful online word processor, designed for collaborative work.

                                                                                              Zoho CRM コンテンツ






                                                                                                Nederlandse Hulpbronnen


                                                                                                    ご検討中の方




                                                                                                          • Recent Topics

                                                                                                          • Creating Restaurant Inventory Management on Zoho

                                                                                                            Hi,  We run a small cloud kitchen and are interested to use Zoho for Inventory and Composite Item tracking for our food served and supplied procured to make food items.  Our model is basically like subway where the customer can choose breads, veggies,

                                                                                                          • To Zoho customers and partners: how do you use Linked Workspaces?

                                                                                                            Hello, I'm exploring how we can set up and use Linked Workspaces and would like to hear from customers and partners about your use cases and experience with them. I have a Zoho ticket open, because my workspace creation fails. In the meantime, how is

                                                                                                          • Zoho Forms to Zoho CRM : First/Last Name to just Name ?

                                                                                                            When integrating a Zoho Form into the Accounts menu of the CRM I'm having trouble with how names are formatted ; In Forms the data is available as First Name or Last Name In the CRM there is only one field called Name How can I ensure that "John" "Smith"

                                                                                                          • This festive season, offer discounts with coupon code support in Stripe Checkout

                                                                                                            Hello form builders! It’s the festive season, the perfect time to spread joy and great deals! Now, with Zoho Forms’ latest enhancement for Stripe Checkout, you can do exactly that with coupon codes! Your payment forms integrated with Stripe Checkout can

                                                                                                          • Cómo creo una factura negativa o de abono?

                                                                                                            NEcesito anular una factura y crear una nueva igual pero en negativo. El sistema no me lo permite

                                                                                                          • Power of Automation::Streamline log hours to work hours upon task completion.

                                                                                                            Hello Everyone, A Custom Function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as to when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:-

                                                                                                          • CRM : Function to add user name to text field

                                                                                                            I have a lookup field in a module that is linked to the CRM users so we can assign a Project Lead to the customer. Sadly Zoho Marketing Automation doesn't sync Lookup fields so I need to extract information from the lookup to text fields: Lookup field

                                                                                                          • Zoho CRM - Restrict Login based on work hours

                                                                                                            Hi there, I'm wondering if we can restrict users to login during works - For example the users would be able to login from 8am to 5pm. I have seen the IP address restriction - the only downfall is what if the customer has dynamic IP. thanks Jiri

                                                                                                          • Tips and Tricks #46: Customize themes and templates using Show's Master View

                                                                                                            Hi All!  Let's say you want to revamp your presentation and make changes to its visual design. You can do this easily using the Master Slide and its associated layouts. The Master Slide stores information about all the layouts used in the presentation.

                                                                                                          • Sheet View in CRM portal

                                                                                                            Hi, When will it be possible for my CRM portal users to edit/add records with Sheet View? George

                                                                                                          • What's New in Zoho Invoice | July - September 2025

                                                                                                            Hello everyone! We’re back with the latest updates and enhancements we’ve rolled out in Zoho Invoice from July to September 2025. Here’s what’s new this quarter: Introducing the Singapore Edition in Zoho Invoice Share Invoices through WhatsApp GST 2.0

                                                                                                          • Alert: Audio Call Support to be discontinued for Old Live Chat Widget from December 31, 2025

                                                                                                            Action Required: Upgrade to the New SalesIQ Live Chat Widget We're reaching out with an important update regarding the SalesIQ Live Chat Widget that requires your immediate attention. Effective December 31, 2025, audio call functionality will no longer

                                                                                                          • multiple contacts for one account

                                                                                                            We currently use Zoho CRM where each Account represents a club or organization, and each Contact represents a manager or owner. However, some of our managers own or manage multiple clubs, and Zoho only allows a contact to be linked to one account at a

                                                                                                          • Rich text Merge field - Not using font specified in HTML

                                                                                                            I have a rich text merge field in a writer template which is creating a table. I have chosen to use this method instead of a repeat region because I need to specify specific cell background colours which change every time the document is created. The

                                                                                                          • ヒートマップ詳細設定について

                                                                                                            はじめまして マーケティング担当の浅田です。 PageSenceのヒートマップ設定について質問です。 単一ページ毎の設定は上手くできるのですが 詳細設定にて、トップページのURLを含ませ全体のヒートマップを計測できないか試したところ お知らせ：データを受信していません。と表示されてしまいます。 トップページURLで始まるページの条件も試しております。 全頁を反映させたいと思い、詳細設定を上手く設定できたらと考えております。 どなたか詳しい方がいらっしゃいましたら、教えて頂けませんでしょうか 宜

                                                                                                          • Automatic category assignment

                                                                                                            Hi, I’d like to ask if there is a way to automatically assign an expense category based on the recognized Merchant. What would be the simplest way to set up automatic category assignment? Alternatively, is there an option to first choose the category

                                                                                                          • Experience with Zoho Vertical Studio

                                                                                                            I'm considering Zoho Vertical and would love to hear from some devs who've been using it. The Zoho ecosystem is pretty solid, so I assume the experience has been pretty good, but sometimes Zoho has its quirks. Overall, has your experience been positive?

                                                                                                          • LinkedIn Chrome Extension

                                                                                                            Hello - I believe it is a known issue that the LinkedIn extension Resume Extractor has stopped working and they are working on potential fixes. Wondering how others users are finding this issue and if there are any better workarounds for this issue? It

                                                                                                          • Advance PDF creation from CRM data

                                                                                                            I'm trying to create a PDF export of data in the CRM. My problem is I want a pretty complicated format for the data. I'm trying to export multiple modules worth of data, with nested one-to-many relationships between the modules. Along with that, I want

                                                                                                          • Sub-Form Padding in CSV Export

                                                                                                            Hi, When you use the Sub-Form, and for example you have a Date Field on the Main Page, then Option 1 and Option 2 fields on the Subform, when you export this to CSV the Date column will only have the Date in 1 row, the first row, it would be nice to pad

                                                                                                          • More Formula Functions

                                                                                                            Hi, I would like for example to be able to have a Date Field and Formula Fields, and then in the Formula Fields I would like to grab just the Month of the above Date Field or the Week Number of the above Date Field. So more "Functions" than the current

                                                                                                          • File upload size limits

                                                                                                            I am designing a contact form in Zoho Creator with File upload facility. I am not sure where and how to set the following properties for the same: Allowed File Types Maximum Size of the Attachment Number of Attachments

                                                                                                          • Composite Services and Account Tracking

                                                                                                            I am looking to garner support/request the ability to make composite services. A quick search in the forums brings up multiple requests for this feature. I fail to see why an item is mandatory while services are optional. I also would like to see the

                                                                                                          • All operation codes getting Not applied in WO-TT22J1025WOR0471, SR-TT22J1025PRE0423,FOR VIN-W1K6G2AB2SL005130.

                                                                                                            Dear Support Team, Please refer attached screenshot. All operation codes getting not applied in work order. Request you to please check and update ASAP.

                                                                                                          • Exclude Segment from Campaign Recipients

                                                                                                            I've created two Segments in order to separate Non-Marketing Contacts from Marketing Contacts. I'd like to send an Eblast to all Marketing contacts in my lists, but when I go to select Recipients, I have two options: 1. To Choose Lists to Send to; 2.

                                                                                                          • Reschedule Multiple/Mass Calls at Once

                                                                                                            When we go into a Call record, we have the option to "Reschedule Call." How can we select multiple Calls at once from the Activities tab and Reschedule them? The big use case for us: We have many leads that our reps are supposed to call on a daily basis.

                                                                                                          • Option to Empty Entire Mailbox or Folder in Zoho Mail

                                                                                                            Hello Zoho Mail Team, How are you? We would like to request an enhancement to Zoho Mail that would allow administrators and users to quickly clear out entire folders or mailboxes, including shared mailboxes. Current Limitation: At present, Zoho Mail only

                                                                                                          • I want to Make the due date of the project appears automatically

                                                                                                            hello everyone I want to know if the zoho projects app can add automatically the END DATE of the project I added the duration, start after dates of each tasks in the project template & the starting date of the project itself and the end date of it doesn't

                                                                                                          • Drag 'n' Drop Fields to a Sub-Form and "Move Field To" Option

                                                                                                            Hi, I would like to be able to move fields from the Main Page to a Sub-Form or from a Sub-Form to either the Main Page or another Sub-Form. Today if you change the design you have to delete and recreate every field, not just move them. Would be nice to

                                                                                                          • Deluge Tips & Tricks: Mastering dates and times

                                                                                                            Hello everyone! We're bringing back our Deluge Tips & Tricks series, where we show you how to make the most of Deluge for achieving a wide range of use cases. Working with dates and times is a fundamental part of almost any business application. Whether

                                                                                                          • Adding a Button in a Form in Zoho Creator App

                                                                                                            Hi I want to know how can I add a Button in Zoho Creator form. I have options that I select from a multiselect options and I want the button to add those selected options in a Subform. I do not want to add on a Page Builder I want it inside a specific

                                                                                                          • Internal Exception for Creator function

                                                                                                            I have been running a script successfully for several days using the scheduler in my application. Now today it suddenly creates and Internal Exception error. Nothing has changed about the script. The data is still retrievable from the web service it is using. Why would this suddenly be happening for no apparent reason? Also noticed that my usage logs are not up to date. Is something changing behind the scenes?

                                                                                                          • How to Set Up a Custom Domain and Customize Homepage

                                                                                                            Hello, I am experimenting with Zoho Creator to build applications for my own use case and have encountered a couple of issues: 1. Custom Domain Access: Is it possible to access my Zoho Creator application through a domain that I own? If so, how does the

                                                                                                          • Announcing new features in Trident for Mac (1.26.0)

                                                                                                            Hello everyone! Trident for macOS (v1.26.0) is here with new updates to securely manage your passwords, locally save your emails, and protect against emails with suspicious URLs. Let's take a quick look at them. Manage passwords with Zoho Vault eWidget.

                                                                                                          • Default Value on a Number

                                                                                                            Hi, I would be nice to also be able to set the Default Value for a Number Field, not just the Initial Value shown, so if not on the screen so not enetred it can have a value of 0 for example. Thanks Dan

                                                                                                          • Having trouble fetching contents of Zoho Connect Feeds using the API, requesting alternative API documentation.

                                                                                                            I'm trying to retrieve feed/post data from Zoho Connect using the API but facing challenges with the current documentation. What I've tried: OAuth authentication is working correctly (getting 200 OK responses) Tested multiple endpoints: /pulse/nativeapi/v2/feeds,

                                                                                                          • Tip#45: Custom Tags and Cluster Tags

                                                                                                            Tagging your work items helps categorize and label work items within your Zoho Sprints workspace. Now, this feature is expanded across multiple modules, along with the introduction of cluster tags that offer an advanced tagging mechanism. Manage Tags

                                                                                                          • Extensions 101 webinar series: Build, integrate, and monetize with extensions

                                                                                                            Attention developers! Are you ready to take your extension development skills to the next level? We're excited to bring back the Extensions 101 webinar series with an expanded lineup of Zoho products and an introduction to more platform features. Last

                                                                                                          • [Free Webinar] User Management in Zoho Creator - Creator Tech Connect

                                                                                                            Hello Everyone! We welcome you all to the upcoming free webinar on the Creator Tech Connect Series. The Creator Tech Connect series is a free monthly webinar that runs for around 45 minutes. It comprises technical sessions in which we delve deep into

                                                                                                          • A couple of minor enhancements to Workflows

                                                                                                            Last updated on September 17, 2024: These enhancements were initially available for early access, and we've now enabled them for all users. We are elated to announce a couple of enhancements to custom functions in our Workflows! Say hello to: "Source"

                                                                                                          • Next Page