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

• Add Reporting feature to display variance/change columns when comparing periods

  When running reports to compare periods (for example, Profit and Loss comparing current year to previous), I would like to be able to display variance columns in both (a) amount or (b) percentage.

• Link Contacts to Billed Accounts

  Hello, I want to do a survey on all my customers of 2025. For that I want to select all contacts linked to accounts who where billed in 2025. How to I create this link to I can then use Zoho Survey with this database of contacts?

• Historical Sales Info - Blend with Finance Invoice Line Items, Access in CRM and Desk

  My company has been using Zoho One since 2021, with sales data going back through 2020. However, we have been in business much longer, and we have historical sales information that we want to have at our fingertips when talking with customers (usually

• Pre-Zoho Sales Info - Best Way to Add to Desk / CRM

  My company has been using Zoho One since 2021, with sales data going back through 2020. However, we have been in business much longer, and we have historical sales information that we want to have at our fingertips when talking with customers (usually

• CRM function REST API response format

  Is there a way to control the JSON response returned by the CRM function REST API? If I call a function using either OAuth or an API key it returns a 200 OK response with a string in the format shown below. I am using a particular feature of an external

• Using MPN across multiple SKUs and inventory tracking

  I have several different SKU's that share a common MPN and would like to track inventory by MPN. SKU1 has MPN1 assigned SKU2 has MPN1 assigned Here is an example If I start with 5 of MPN 1 in stock I want each SKU1 and SKU2 to show as 5 in stock, If I

• Unable to Access Application:

  Whenever I try to access my application from the desktop, say I am editing it and want to test something in the desktop environment I get: An error has occurred. An internal error has occurred. Please check the URL , or try refreshing the page I can edit

• Evernote (ENEX) import limitations

  I have been with Evernote since 2010, but the latest price increase is ridiculous. I am currently testing Zoho Notebook as a replacement. I am impressed so far - if it were not for critical need to import legacy Evernote notes, I would 100% migrate to

• Cannot see Application from Lookup field

  Hi all, I am trying to access data for an application on our account via a lookup field; however, the application doesn't appear in the dropdown at all. Can anyone shed any light on this, please? I have asked Zoho support; however, they're just as confused,

• Zoho CRM Meetings Module Issues

  We have a use-case that is very common in today's world, but won't work in Zoho CRM. We have an SDR (Sales Development Rep) who makes many calls per day to Leads and Contacts, and schedules meetings for our primary Sales Reps. He does this by logging

• Zoho Books integration sync from Zoho CRM does not work

  Hi Zoho Community & Zoho Support We just tried to get a sync some products into Zoho Books from CRM using the native sync and we're getting an error: "It looks like some mandatory fields you're trying to map are empty. Please provide valid field names

• Disable Auto Send Email Notification in Invoice Creation

  Here is what I am facing Make payment via Razor Pay (All Good) Capture payload data on my webapp Send this data, along with other data to Zoho books to create the invoice However in this step - An email goes to the client + The Invoice remains as draft

• P & L Sub-categorized accounts

  How can I show sub-categorized Income and Expense accounts on the P & L report?

• Report showing Bill Details with Project and Sales Invoice Number

  Hi There, I am hoping that someone can help, I am looking for report that can show the bill and expense details along with project its as assigned to and the invoice number that the sales has been raised in. The goal is I can filter a customer/project

• Advanced Payment for Inventory Items with serial numbers

  Hello, We sell equipment that we track the unique serial numbers on using Sales Orders. We can charge the customers an advanced payment, then the balance on delivery. We cannot figure out a way to do this in Books/Inventory: - Cannot part invoice a SO

• Is it possible to restrict ZCRM user to see only custom views created by administrator

  I have segmented data in my CRM and I want to allow different users to be able to see only parts of it based on some criteria. I've tried to create and share a custom view, but then there is always an option for user to see all open lead for example.

• Issues Logging into ZOHO

  Hello, one of my coworkers is having issues logging into ZOHO, she has requested a code when entering and the email is correct but she has not received the code. can you help us with this?

• Google Fonts Integration in Pagesense Popup Editor

  Hello Zoho Pagesense Team, We hope you're doing well. We’d like to submit a feature request to enhance Zoho Pagesense’s popup editor with Google Fonts support. Current Limitation: Currently, Pagesense offers a limited set of default fonts. Google Fonts

• Add Popup Rejection Metrics to Reports

  Hello Zoho PageSense Team, We would like to request improved reporting for popup interactions. Current Limitation: PageSense currently provides conversion data, but there is no clear visibility into: Popup rejections Popup closes (✕ button clicks) Dismissals

• Ability to Reset / Reinitialize Popup Cookies

  Hello Zoho PageSense Team, We would like to request the ability to manually reset popup cookies. Current Limitation: At the moment, it is not possible to initiate a new popup cookie from the our side. Visitors who rejected or closed a popup will not see

• Control Popup Cookie Expiration Duration

  Hello Zoho PageSense Team, We would like to request an enhancement related to popup cookie management. Current Limitation: Currently, PageSense popup cookies remain active for 365 days, and this duration cannot be modified by us. If a visitor closes or

• Clone / Export Popup Design Across PageSense Projects

  Hello Zoho PageSense Team, We hope you’re doing well. We would like to request an enhancement that allows popup designs to be reused across different PageSense projects. Problem Statement: Currently, Zoho PageSense allows popups to be duplicated only

• Are there settings for hyperlinks?

  Clicking a hyperlinked cell in Sheet creates this little pop-up with the actual hyperlink inside. Is it possible to have a 1-click link where if you click the cell it opens the link directly with no pop-up?

• Automatically include all ticket attachments in the ticket resolution email

  Hello Zoho Community, We are implementing Zoho Desk in a real customer-facing production environment and have run into a limitation that is becoming a blocking requirement for our clients. The problem When a ticket is closed or resolved, Zoho Desk sends

• Keep Zoho People Feature Requests in the Zoho People Forum

  Hello Zoho People Product Team, Greetings. We would like to submit a feature request regarding the handling of feature requests themselves, specifically for Zoho People. Issue: Feature Requests Being Moved to Zoho One Zoho People feature requests are

• Can I write a check in Zoho Books with no associated bill?

  This currently does not seem possible, and I have a client that desperately needs this function if I am able to convert them with Quickbooks. Thank you in advance for your reply. 

• Finding text within a ticket: Expand All or Search this Ticket

  The auto-collapse feature within a ticket is nice for screen scrolling, however it makes it difficult to find text within the ticket if the email is collapsed. In fact you cannot find text if it is collapsed. I would like to propose a feature that allows

• Books & Desk. Client mapping

  Hi, I’ve been using Zoho Books for several years and am now looking to improve my customer service. I'm experimenting with Zoho Desk and want to sync and map my client data from Zoho Books. However, it seems that mapping requires both contacts to have

• String handling

  If I cut a currency string from a quote and try and paste it into the Deal "Amount", it will fail unless I manually delete any commas. Dollar signs are no problem, but comma's seem to fail. Please correct this Input Validation error.

• Allow Stripe Credit Card and Stripe ACH payment methods to be enabled separately on an invoice.

  I need to be able to pick at the invoice level whether Stripe Credit Card and/or Stripe ACH payment methods are available. Currently, I'm not able to select from the two Stripe payment methods individually on an invoice. However, there are some larger

• SKUs for Invoices, POs, etc.

  It doesn't appear that one can enable SKU display on invoices, POs, etc. This is problematic, and I don't see a good reason why this shouldn't be an option. Some of our vendors and customers use this in their system. Every other identifier code is available

• Feature Request - Allow Customers To Pick Meeting Duration

  Hi Bookings Team, It would be great if there was an option to allow customers to pick a duration based on a max and minimum amount of time defined by me and in increments defined by me. For example, I have some slots which are available for customers

• YouTube Live streaming? how to? Zoom has this feature, built-in. Can't find it on zoho meetings.

  YouTube Live streaming? how to? Zoom has this feature, built-in. Can't find it on zoho meetings.

• Feature Request - A Way To Search Item Groups

  Hi Inventory Team, I can't find any way to filter or search by fields of Item Groups. It would be great to see that functionality added. I have a use case where a single product might come from 5 or more suppliers and each supplier's item is an Item in

• Feature Reqeust - Include MPN In Selectable FIelds

  I have noticed that the MPN is not available to show in the list view of Items. Please consider adding it as EAN, UPC and ISBN are all available, so it doesn't make much sense to exclude this similar option. Thanks for considering my feedback.

• Feature Request - Option To Hide Default System Fields on Items

  Hi Zoho Inventory Team, As far as I know it is not possible to hid some of the defult system fields on Items, such as UPC, MPN, EAN, ISBN. A good use case is that in many cases ISBN is not relevant and it would be an improved user experience if we could

• Making an email campaign into a Template

  I used a Zoho Campaign Template to create an email. Now I want to use this email and make it a new template, but this seems to be not possible. Am I missing something?

• Campaigns does not work!

  I am running into so many problems trying to use Zoho Campaigns, that I am seriously considering dropping the app from my (shrinking) list of Zoho applications I actually use. Apart from having to fight the software trying to create a design and email,

• Feature Request - Make Available "Alias Name" Field In Item List View

  Hi Zoho Inventory Team, I have noticed that the "Alias Name" field does not appear on the list of selectable columns in the Customise Columns feature in the Items module. This would be very useful to see for businesses who are using the Alias Name field

• product name field issue-n8n

  Hey guys, I have a question. I want to create a new product using the workflow. The problem is with the product name field; I don't know how to fill it in. The workflow starts with retrieving information from the leads table, retrieving links to scrape

• Next Page