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

• Topic Participants

• Janani Thakshayani

  

• Wayne Sobey | Amacron Golf & Turf

• Vaijayanthi V

• 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

• Presenting ABM for Zoho CRM: Expand and retain your customers with precision

  Picture this scenario: You're a growing SaaS company ready to launch a powerful business suite, and are looking to gain traction and momentum. But as a business with a tight budget, you know acquiring new customers is slow, expensive, and often delivers

• Can Zoho Flows repeat Actions more than once?

  I'm attempting to make an intentional Zoho Flow loop using the below layout. However, when "WithinLimit" condition is met, the program fails to execute the action "Get & Add Request Co..." again. Is this by design? Is Zoho Flows unable to repeat actions

• Video interviews not compatible on mobile

  Hi Zoho,  The 2-way video interview feature you have added to Recruit would be great if the candidate was able to use the link to the video on a mobile phone, it doesn't work on Samsung browser, it doesn't work on chrome/firefox mobile version of browser,

• Zoho Desk View Open Tickets and Open Shared Tickets

  Hi, I would like to create a custom view so that an agent can view all the open tickets he has access to, including the shared tickets created by a different department. Currently my team has to swich between two views (Open Tickets and Shared Open Tickets).

• Enviro Recycling EHF Fee's

  ​​I'm in Canada and each province has it's own recycling program with it's own fee's. There are numerous categories that electronics fall into and each category has a taxable rate applied to it. I need a way to be able to apply the correct rate depending

• Zoho Desk API - Influence which layout is used

  Hello, how can the ticket layout be changed using the API? I would like to choose the layout directly when creating the ticket. If this is not possible, my question would be how can I change it afterwards? Best regards, Sven

• Has CSS Changed for Common Elements?

  I noticed today that the standard title headings on my Forms and Reports display smaller in size than before. Google Dev Tools (attached), seems to confirm my suspicion. Something appears to be overriding the 1.375rem to 1.125rem. The font size on the

• Text field alignment

  Is it possible to align text in the text field? Or even better, is there a possibility to have a field which accepts only numbers?

• email moderation issue when email is sent in the name of a mail group

  Symptom: an email that is sent by a mail group moderator in the name of a moderated mail group is held back for approval. Reproduction: Create a moderated mail group with members and moderators. Allow that mails can be sent in the name of the group (extended

• Pipeline in Custom Modules

  I love the way the Sales Pipeline looks and functions with reports. I would like to add the save pipeline features and visualization to a custom module, however, I only see that these pipelines are only available for the Deals module. Is there a way to add pipelines to custom modules?

• Tip #53- Remote Billing: Your End-to-End Billing Solution in Zoho Assist- 'Insider Insights'

  Manual invoicing, after every remote support session, can be time-consuming and often error-prone. As an MSP, IT admin, or even a freelance support technician, it may become overwhelming to keep track of session time, service rates, and client invoices.

• Record history and ticket interaction tab in contacts and accounts

  Hello everyone! We have improved the History tab to help users trace updates in the interaction activity logs of the Contacts and Accounts detail pages in the Customer Module. This enhancement make it easier to get clear details about who made each update,

• Is there an API to "File a Ticket" in Desk

  Hi, Is there an API to "File a Ticket" in Desk to zoho projects?

• Keyboard UX for Assemblies

  The new Assembly module has a counter-intuitive behavior that ought to be corrected. When an Assembly is ready to be entered, there are two options given, the blue-highlighted "Assemble" and the gray "Save as Draft". This correctly implies that the normal

• Why can't I see images uploaded by other users in the Library for Campaigns

  We are several users of zoho Campaign. I have uploaded visuals of our company, but my team members can't see them.

• zoho sheet stuck

  I Need help. ZOHO sheets stuck on the loading screen. I've already deleted the system cache and cookies of my browser (google chrome) but it's still not opening. 

• Tip of the Week #78 – Cut response time with multichannel shared inboxes

  If you’ve ever felt your team juggling between multiple email accounts, social pages, and chat apps just to reply to customers, you’re not alone. Managing conversations in multiple channels can quickly turn messy. Important messages across inboxes, replies

• Need a way to run a client script longet than 10 seconds

  By The Grace of G-D. Hi, Currently, Client Scripts are Timing out at 10 seconds. We have complex logics that needs more time. Can you add a feature request to increase the timeout?

• FSM Improvement Idea - Show an Import button when there is no data

  I am setting up FSM for a client and I noticed that there is no option to import data, see screenshot below. Even when you click Create Contact there is only an option to Import from Zoho Invoice. It is only after you add at lease 1 record that the Import

• UI Improvement - Ability to Collapse Flow

  The UI for Flow is generally pretty good. However, when multiple decision trees are used, the layout can get pretty convoluted and hard-to-follow (see one of my Flows below): In these cases, even the auto-arrange fails to make this something that a normal

• Tasks Statuses

  Hi, The task status "Completed" is a final status which closes the task. We need to have a status "Cancelled". However, when the status is set to "Cancelled", the task prompt still has a blue button to Close Task. When the customer clicks that and closes

• Add Lookup Field in Tasks Module

  Hello, I have a need to add a Lookup field in addition to the ones that are already there in the Tasks module. I've seen this thread and so understand that the reason lookup fields may not be part of it is that there are already links to the tables (

• Create New Tasks Layout in CRM

  I am able to do this in Leads, Contacts, Meetings, Calls - every other module, but cannot create a new layout in tasks. I have the appropriate access but it's simply not appearing as an option. Only "Standard" option shows. Please help!

• Using IMAP configuration for shared email inboxes

  Our customer service team utilizes shared email boxes to allow multiple people to view and handle incoming customer requests. For example, the customer sends an email to info@xxxx.com and multiple people can view it and handle the request. How can I configure

• Auto-sync field of lookup value

  This feature has been requested many times in the discussion Field of Lookup Announcement and this post aims to track it separately. At the moment the value of a 'field of lookup' is a snapshot but once the parent lookup field is updated the values diverge.

• Zoho Recruit Subscription

  Hello Zoho Recruit Team, Good day! I would like to inquire about your recruitment subscription plans and would also like to verify the current subscription our company is enrolled in under Zoho Recruit. Thank you, and I look forward to your response.

• Is it possible to create a meeting in Zoho Crm which automatically creates a Google Meet link?

  We are using Google's own "Zoho CRM for Google" integration and also Zoho's "Google Apps Sync" tools, but none of them provide us with the ability to create a meeting in Zoho CRM that then adds a Google Meet link into the meeting. Is this something that

• Automatic Portal invite

  We have numerous customers we move through a blueprint in deals, when they get to a certain point we need to give them portal access, how can this be done through deluge or a workflow? Latest Update (December 2025): The option to automate portal invitations

• Marketer's Space: Why mobile optimization deserves a place in your email strategy

  Hello Marketers, Welcome back to Marketer's Space! Today, we'll talk about the importance of creating mobile-friendly email designs. While mobile phones were once used only to make phone calls, today they're used for almost everything, including texting,

• Enhancements in Canvas

  Dear All, Greetings! Canvas lets you design the record details page to suit your brand or business preferences. We are glad to introduce the following enhancements to uplift your design experience. Reusable Components Style Presets Let's go! Reusable

• Introducing Dark Mode / Light Mode : A New Look For Your CRM

  Hello Users, We are excited to announce a highly anticipated feature - the launch of Day, Night and Auto Mode implementation in Zoho CRM's NextGen user interface! This feature is designed to provide a visually appealing and comfortable experience for

• ABN with Legal Entity Name

  Hi, How can I execute this? The ABN number is entered in Accounts Module and ideally, it should display or suggest the correct Legal Entity Name based on the ABN lookup. ex. Account Name: JPG Resources ABN Number: 65 067 761 871 Legal Entity Name: (auto

• Issue: Ticket Export Does Not Include Ticket Threads

  Dear Zoho Desk Support Team, I hope you’re doing well. I wanted to bring to your attention that the current ticket export feature in Zoho Desk does not seem to include the ticket threads or conversation history. When exporting tickets, only the summary

• Pushover Notification Module

  Hello, it would be good if there would be a "Pushover" (https://pushover.net/) module besides the standard SMS module. Pushover is now very well known, especially in IT, and is becoming more and more popular. The biggest advantage are the customizable

• Forward - no Ticket Number

  Hello, when I send an email to Zoho Desk via Reply or Reply All, the ticket number is in the subject line. But if I use forward then not. Is there an option to change this? We often forward e-mails and a reply to this will of course create a new ticket

• Checkout: Adding Images

  Hello everyone, I’m trying to add a small GoGreen logo in the shipping section of the checkout to promote our sustainable shipping. While I can insert text without any issues, it seems that adding images is not supported in this area. Is there currently

• [solved] #original_sender trick not working for us

  Hello community ! We really like DESK.. it's working like a charm for us but I have a request. Some users are sending emails personnaly to me (instead of using the support adress).. and I saw there was a possibilty to use : #original_sender {mail@mail.com}

• SalesIQ Chat Notifications

  I am the admin of our salesIQ implementation. About two weeks ago, I started hearing/seeing notification for ALL chats messages from monitored agents/chat participants. I don't need to see these, we have a manager who deals with this. I can't stop the

• Correlated subqueries not supported in Zoho Analytics. This creates huge limitations

  Running into a major limitation in Zoho Analytics: correlated subqueries simply don’t work, even in completely standard SQL patterns inside a JOIN. Example: LEFT JOIN "Bills" b ON d."Id" = b."Deal ID" AND EXISTS ( SELECT 1 FROM "Bill

• Batch Emails in CRM Plus without using a template?

  Hi guys. Is it possible to send the same email to multiple people at the same time within CRM Plus without using an email template? At the moment we create a custom view in Contacts to display the people that we need. We then click the boxes on the left, and click 'Send Mail', but always need to select a template. Sometimes its better to write a quick one off message, without needing to set up a template first. Thanks.

• Next Page