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

• How to print envelope labels from Zoho CRM

  Can anybody give me any clue how to print envelope/package labels directly from Contacts view? Regards, Alexandru Moderation Update: The Canvas Print View, which also facilitates the formatting of mailing labels, is now available! Learn more here: Zoho

• Data Import: New interface, improved field mapping, and more

  Hello everyone! We’ve redesigned the data import process to ensure accurate field alignment and verify that every column in the uploaded file maps correctly with Desk fields before import. Here's how: Streamlined Import Steps Importing data is now more

• Address labels

  Hi, we've been searching for a long time for a simple way for our employees to print address labels using a Dymo Label Writer 450. We came up with two alternatives that work, bur aren't really optimal.  First one is WebMerge. Webmerge is a great application that does 100% what we need but it is way too expensive for our needs. We only need to print a couple of labels per month. The second one is the integrated print tool in Zoho CRM, (Print preview) You would think this is the obvious way to do this

• Print Labels

  How can I configure my address to print my labels like this: Name Mailing Address Mailing City, Mailing State Mailing Zip Country Instead of: Name Mailing Address Mailing City Mailing State Mailing Zip Country Thanks!!!!

• Mailing labels - Improperly formatted

  All I'm trying to do is print one, properly formatted, mailing label. I'm in the Contacts module. In the default "list view" for mailing labels I selected a single client, hit the "more actions" drop down and selected print mailing label. Unfortunately,

• Printing mailing labels

  The ability to print mailing labels would seem to be an important, basic, function of a good CRM. I find it very surprising that this has still not been addressed adequately by Zoho. When trying to use the existing "mailing label" included: 1. the data

• Pageless mode needed to modernise Writer

  When we switched from GSuite to Zoho, one of the easiest apps I found to give up, was Docs. In many ways, Writer has always been more powerful than Docs, especially in terms of workflows/fillable forms/etc. However, I went back into Docs because I notice

• Zoho Projects - Visual improvement to parent and sub-task relationship

  Hi Projects Team, My feature request is to improve sub-task visibility. Please see screenshot below. I really think parent child relationships could be visually improved. Even if the first letter of the parent task was inline with other same level tasks

• API method to get activity feed in Recruit

  Hi community, I'm trying to figure out - is there any API method tto get information about datetime when Recruit/Candidates record tag where added?

• Printing Mailing labels

  Is there any way to adjust the size of the printing labels? or product would I use to print labels from Zoho? Thanks, Josef Krieger Moderation Update (14th April 2025): We have another post discussing the same topic with votes and feedback from users.

• Default to Current Date

  I'm importing data from a excel spread sheet that does not have date column and I'd like the date column in the Zoho Database to default to the current date. Any way I can do this?

• Different Company Name for billing & shipping address

  We are using Zoho Books & Inventory for our Logistics and started to realize soon, that Zoho is not offering a dedicated field for a shipping address company name .. when we are creating carrier shipping labels, the Billing Address company name gets always

• Make Packages from multiple sales order of a single customer

  Our customers sends orders to us very frequently, some times what customer wants is to ship items from 5 to 6 sales orders in a single shipment. it will be very nice if, zoho can implement this function, in which we can select items from other sales orders of the customer.

• Show Custom Button in Portal Listview Canvas

  I have created a custom button that shows in a list view of deals (internally I can see it). I have permissions to allow this button on the portal. But it is not displaying in the canvas list? Before I do too much leg work, is this function allowed?

• Zoho Sites "pages" management page

  I have 80 plus pages on zoho sites. When I go to the "pages" link to view and edit pages, They are not in any kind of order, so I spend lots of time searching for pages when I need to edit or create new. How can I change the view order of all my pages

• AI feature in Zoho Desk suggesting answers based on past ticket threads

  Hi I would like to suggest something that would be very useful : instead of suggesting answers based on the Knowledge Base, I think it would be great if Zia could analyze the history of all customer and agents threads, to suggest answers in new tickets.

• Advanced Customization of the Help Center using JavaScript

  Hello everyone, The Help Center in Zoho Desk can be customized by using HTML and CSS to provide structure and enhance the page's appearance—but what if you want to add interactive and dynamic elements? You can add these effects with JavaScript, a programming

• Introducing Zoho POS for the Kingdom of Saudi Arabia

  Hey everyone, We are excited to kick-start December with a completely personalized edition of POS for retail businesses in Saudi Arabia to help run your operations with ease. It offers four different subscription plans—Free, Standard, Professional, and

• Unveiling Zoho Sites 2.0 - A new dimension in website building

  Dear Zoho Sites Users, We are thrilled to announce the launch of Zoho Sites 2.0 today! This refresh represents a significant step forward in the capabilities of Zoho Sites and is crucial for creating a lasting and positive impact on our customers' businesses.

• Script Editor not an option

  I am trying to apply a script to a sheet and Script Editor is not an option. I don't want to go outside Sheets to do this (like Creator) if it can be done inside Sheets.

• Not able to link email text.

  Kindly check Zoho Sites. I am unable to turn a text into a email link. The save button does not work. Kindly try yourself to see it not responding to save.

• Migrate data from old to new account

  Hy, Have one Old Zoho Notebook Account with Data , want to migrate that whole Data to New Zoho Notebook Account which is in Zoho One . Is that possible ? If Yes then how?

• Zoho Analytics Regex Support

  When can we expect full regex support in Zoho Analytics SQL such as REGEXP_REPLACE? Sometimes I need to clean the data and using regex functions is the easiest way to achieve this.

• Add Custom Reports To Dashboard or Home Tab

  Hi there, I think it would be great to be able to add our custom reports to the Home Tab or Dashboards. Thanks! Chad

• landed cost-need help with different currency under the same bill

  I’m having trouble recording landed costs in Zoho Inventory/Books. My purchase order is in CNY, but the landed cost (freight) I pay is in USD. Zoho forces everything under the same bill to use one currency, so I can’t enter the landed cost in its actual

• Pricing Strategies: #1 Nuances in Pricing

  When Clara first opened her digital printing shop, pricing was simple. She sold handmade greeting cards, planners, business cards, and other physical items at fixed label prices, individually and in bulk. One SKU, one price, one bill, and that's all it

• [Free Webinar] Learning Table Series – Education Management in Zoho Creator

  Hello Everyone! We’re excited to invite you to another edition of Learning Table Series, where we showcase how Zoho Creator empowers industries with innovative and automated solutions. About the Learning Table Series The Learning Table Series is a free,

• Move attachments from one module to another with Deluge

  I have created a button that works just like the convert button for my custom modules. I would like this custom function to move any attachments in this record to the new module. I can't seem to find any documentation on how this can be accomplished.

• Help in function code

  Hi, could someone look at the code below and tell me what 's wrong with it? After a deal creation or edition the code should find the related Account, than all open Deals for that Account and copy the field Total_Open_Deals from the Account record to

• Automation Series: Auto-update Phase Status

  Hello Folks! You can auto-update your phase's status based on status of underlying tasks using custom functions. In this series, we will showcase how to create and run custom functions, using Deluge, with ease. Follow the steps below and automate your

• how to add subform over sigma in the CRM

  my new module don't have any subform available any way to add this from sigma or from the crm

• Zoho Projects - Project Details on the Project Menu

  Hi Project's team, I've helped may businesses setup and use Zoho Project and one thing I see time and time again is confusion on where to find the Project Details information. I would be much more intuitive if Project Details was on the menu before Dashboard.

• Zoho Projects - Add Feed to Project Tabs

  Hi Projects Team, I'm working on a lightweight communications requirement for one of my customers in relation to communicating with their client users via Zoho Projects. I noticed that the Feed is only available in the Collaboration section, but you can

• Flow - Fetch info from drop down in another module

  I am running into a road block which I thought would be a simple task. My goal - The account is assigned to a "route" which can be selected from a drop down menu and adds a tag to the account accordingly (easy enough). Now when I create a task for this

• Show unsubscribed contacts ?

  Hello, I would like to display the unsubscribed contacts. Unfortunately, I do not have this subscription type as described in the documentation (https://help.zoho.com/portal/en/kb/marketing-automation-2-0/user-guide/contacts/contact-management/articles/subscription-type-24-1-2024#Subscription_Type_field.)

• Zoho Developer Community Hackathon 2025 is LIVE!

  Hey developers! It’s that time of the year again — the Zoho Developer Community Hackathon 2025 is officially open for registrations! If you’ve been waiting for a chance to stretch your skills, try something new, or finally bring that idea to life, this

• Converted Leads Not Showing in Lead Reports

  Converted leads are not showing in the Lead reports. How can I make converted leads visible in the report,

• Text widgets in dashboards

  Having a text widget in a dashboard would help immensely. It would allow adding links to related documents, relevant CRM views, etc. It would allow adding explanations of the data displayed in the other widgets, about how to interpret them or about filtering.

• [Webinar] Zoho Writer for content creators and publishing houses

  Managing multiple drafts, edits, and client reviews doesn't have to slow you down. Join our upcoming webinar to see how Zoho Writer helps content creators and publishing houses create, edit, and publish seamlessly—all in one place. You'll learn how to:

• Adding Reports to Portals

  Is there a way to add Reports to portals so only the user can see report templates relevant to them?

• Next Page