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.
Follow the below steps to do this.
- Query Table - Stock In Hand
- Query Table - Inventory Age Tier
- 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
-
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.
- Create a new pivot over the Inventory Age Tier query table.
- 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.
- The Click Here to Generate Pivot button allows you to generate the report.
-
Hide the totals by clicking
Show/Hide
for all Totals.
-
Click Sort > Custom Sort for Age Tier column to arrange the age tier.
-
Now apply the required Theme.
-
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 Developer Community
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
Zoho TeamInbox 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
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Bug in allowing the user to buy out of stock items
Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres anyReplace Lookup fields ID value with their actual name and adding inormation from subforms
Hi everyone, I wanted to see if someone smarter than me has managed to find any solutions to two problems we have. I will explain both below. To start we are syncing data from Zoho CRM to Zoho Analytics and I will use the Sales Order module when givingCan a Zoho Sites page be embedded into another website (outside Zoho)
Hi All, We have a request from a client - they'd like to take one of our information pages created in Zoho Sites and embed it into their own website? I was told through an email with Zoho that this was possible >>Thank you for your patience regardingBug in allowing the user to buy out of stock items
Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres anyTransition Criteria Appearing on Blueprint Transitions
On Monday, Sept. 8th, the Transition criteria started appearing on our Blueprints when users hover over a Transition button. See image. We contacted Zoho support because it's confusing our users (there's really no reason for them to see it), but we haven'tZoho CRM Sales Targets for Individual Salespeople
Our organistion has salespeople that are allocated to different regions and have different annual sales targets as a result. I am building an CRM analytics dashboard for the sales team, which will display a target meter for the logged in salesperson.Transfer all Related Data to new Account Owner
Currently when I change the account Owner I only see the option to change only the open deals But I want the new account owner to take over all the related modules and all the deal stages Is it not possible right now? Am I missing something? Do I reallyHow to Calculate MTTR (Mean Time to Resolve)
We want to calculate MTTR (Mean Time to Resolve) in our Zoho Analytics report under Tickets. Currently, we are using the following fields: Ticket ID Ticket Created Time Ticket Closed Time Ticket On Hold Time We are planning to calculate MTTR (in days)How to export project tasks, including the comments
Hi, how can I export the project tasks, whereby I can also see the comments associated to a specific task? The use-case is that often we use comments to discuss or update a task related ideas. I would like to export the tasks, where we can also see theDoes Zoho Sheet Supports https://n8n.io ?
Does Zoho Sheet Supports https://n8n.io ? If not, can we take this as an idea and deploy in future please? ThanksBigin Android app update: User management
Hello everyone! In the most recent Bigin Android app update, we have brought in support for the 'Users and Controls' section. You can now manage the users in your organization within the mobile app. There are three tabs in the 'Users and Controls' section:Share records with your customers and let them track their statuses in real time.
Greetings, I hope everyone is doing well! We're excited to introduce the external sharing feature for pipeline records. This new enhancement enables you to share pipeline records with your customers via a shareable link and thereby track the status ofLive webinar: Discover Zoho Show: A complete walkthrough
Hello everyone, We’re excited to invite you to our upcoming live webinar, Discover Zoho Show: A Complete Walkthrough. Whether you’re just getting started with Show or eager to explore advanced capabilities, this session will show you useful tips and featuresDeal Stage component/widget/whatever it is... event
Deal Stages I am trying to access the event and value of this component. I can do it by changing the Stage field but users can also change a Deal Stage via this component and I need to be able to capture both values. Clicking on 'Verbal' for instance,Create advanced slideshows with hybrid reports using Zoho Projects Plus
Are your quarterly meetings coming up? It’s time to pull up metrics, generate reports, and juggle between slides yet again. While this may be easier for smaller projects, large organizations that run multiple projects may experience the pressure whenAdd an option to disable ZIA suggestions
Currently, ZIA in Zoho Inventory automatically provides suggestions, such as sending order confirmation emails. However, there is no way to disable this feature. In our case, orders are automatically created by customers, and we’ve built a custom workflowFormula field with IF statement based on picklist field and string output to copy/paste in multi-line field via function
Hello there, I am working on a formula field based on a 3-item picklist field (i.e. *empty value*, 'Progress payment', 'Letter of credit'). Depending on the picked item, the formula field shall give a specific multi-line string (say 'XXX' in case of 'ProgressUnified Directory : How to Access ?
I signed in to Zoho One this morning and was met with the pop up about the upgraded directory (yay!) I watched the video and pressed "Get Started" ... and it took me back to the standard interface. How do I actually access the new portal/directory ?Zoho CRMの流入元について
Zoho CRMとZoho formsを連携し、 formsで作成したフォームをサイトに埋め込み運用中です。 UTMパラメータの取得をformsを行い、Zoho CRMの見込み客タブにカスタム項目で反映される状況になっています。 広告に関してはUTMパラメータで取得できているため問題ないのですが、オーガニック流入でフォーム送信の場合も計測したいです。メールやGoogle、Yahoo、directなどの流入元のチャネルが反映されるようにしたいのですが、どのように設定したら良いでしょうか。 また、Error While Sign in on Zoho Work Drive
Dear Team, I hope this email finds you well. I have recently created a Zoho account and started using it. But while I am trying to log in to Zoho work drive it won't log me in its crashing every time I try it. I have tried it on android app, phone browserChoosing a portal option and the "Unified customer portal"?
I am trialling Zoho to replace various existing systems, one of which is a customer portal. Our portal allows clients to add and edit bookings, complete forms, manage their subscriptions and edit some CRM info. I am trying to understand how I might bestTranslation support expanded for Modules, Subforms and Related Lists
Hello Everyone! The translation feature enables organizations to translate certain values in their CRM interface into different languages. Previously, the only values that could be translated were picklist values and field names. However, we have extendedUnified task view
Possible to enable the unified task view in Trident, that is currently available in Mail?Bigin, more powerful than ever on iOS 26, iPadOS 26, macOS Tahoe, and watchOS 26.
Hot on the heels of Apple’s latest OS updates, we’ve rolled out several enhancements and features designed to help you get the most from your Apple devices. Enjoy a refined user experience with smoother navigation and a more content-focused Liquid GlassImporting data into Assets
So we have a module in Zoho CRM called customers equipments. It links to customers modules, accounts (if needed) and products. I made a sample export and created extra fields in zoho fsm assets module. The import fails. Could not find a matching parentAllow instruction field in Job Sheets
Hello, I would like to know if it is possible to have an instruction field (multi line text) in a job sheet or if there is a workaround to be able to do it. Currently we are pretty limited in terms of fields in job sheets which makes it a bit of a struggleStreamlining Work Order Automation with Zoho Projects, Writer & WorkDrive
Hello Community, Here is the first post in 'Integration & Automation' Series. Use Case :: Create, Merge, Sign & Store Documents in Zoho WorkDrive. Scenario :: You have a standard Work Order template created in Zoho Writer. When a task status is chosenThe dimensions of multilingual power
Hola, saludos de Zoho Desk. Bonjour, salutations de Zoho Desk. Hallo, Grüße von Zoho Desk. Ciao, saluti da Zoho Desk. Olá, saudações da Zoho Desk. வணக்கம், Zoho Desk இலிருந்து வாழ்த்துகள். 你好，来自 Zoho Desk 的问候。 مرحباً، تحيات من Zoho Desk. नमस्ते, ZohoMulti-line address lines
How can I enter and migrate the following 123 state street Suite 2 Into a contact address. For Salesforce imports, a CR between the information works. The ZOHO migration tool just ignores it. Plus, I can't seem to even enter it on the standard entry screen.Accessing Zoho Forms
Hi all, We're having trouble giving me access to our company's Zoho Forms account. I can log in to a Forms account that I can see was set up a year ago, but can't see any shared forms. I can log into Zoho CRM and see our company information there withoutArchiving Contacts
How do I archive a list of contacts, or individual contacts?Cost of good field
Is there a way we can have cost of good sold as a field added to the back end of the invoicing procedure and available in reports?How to add image to items list in Invoice or Estimate?
Hello! I have just started using Zoho Invoice to create estimates and, possibly to switch from our current CRM/ERP Vendor to Zoho. I have a small company that is installing CCTV systems and Alarm systems. My question is, can I add images of my "items" to item list in Zoho Invoice and Estimates and their description? I would like to show my clients the image of items in our estimates so they can decide if they like these items. And I tell you, often they choose more expensive products just becauseIssue with the Permission to Zoho Form
I am getting an error by signing in to zoho form as it is stated that i don't have permission to access this is admin accountCRM templates
Hello everyone, In my company we use Zoho campaigns where we set up all newsletters and we use Zoho CRM for transactional emails. I have created some templates in Zoho campaigns but from my understanding i cannot use those in Zoho CRM, right?Pending Sales Order Reports
Pending sale order report is available for any single customer, Individual report is available after 3-4 clicks but consolidated list is needed to know the status each item. please help me.Zoho Mail SMTP IP addresses
We are using Zoho Mail and needs to whitelist IP for some redirections from your service to another e-mails. You can provide IP address list for Zohomail SMTP servers?Zoho Campaigns - Why do contacts have owners?
When searching for contacts in Zoho Campaigns I am sometimes caught out when I don't select the filter option "Inactive users". So it appears that I have some contacts missing, until I realise that I need to select that option. Campaigns Support haveemail forwarding not working
Your email forwarding service does not work. I received the confirmation email and completed the confirmation, after that nothing and nothing since no matter what I have tried. Shame as everything else was smooth. I spose it's harder to run one of these web based internet mail services than you guys thought!!! can you fix the email forwarding asap PLEASE!Google Ads Conversions Not Being Tracked in Zoho CRM
We have 3 different conversions created in our Google Ads Account. Only one of the 3 conversion types is tracking in Zoho CRM. Our forms are Elementor Forms that are mapped into Zoho CRM. It apprears to me that all leads are showing up in Zoho CRM, butNext Page