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
Where is the settings option in zoho writer?
hi, my zoho writer on windows has menu fonts too large. where do i find the settings to change this option? my screen resolution is correct and other apps/softwares in windows have no issues. regardsDirectly Edit, Filter, and Sort Subforms on the Details Page
Hello everyone, As you know, subforms allow you to associate multiple line items with a single record, greatly enhancing your data organization. For example, a sales order subform neatly lists all products, their quantities, amounts, and other relevantHow to manage task lists in Zoho Desk?
Hello, I use Zoho Desk for IT customer support. I have a list of standard operating procedures (SOPs), including SOPs for onboarding new users, offboarding users, losing a device, etc. These are lists of tasks to be performed depending on the situation.Add specific field value to URL
Hi Everyone. I have the following code which is set to run from a subform when the user selects a value from a lookup field "Plant_Key" the URL opens a report but i want the report to be filtered on the matching field/value. so in the report there isCreating a Chart from a Report
In Zoho Analytics, is it possible to create a chart from a Pivot View report? We are looking to use Zoho Analytics to replace Excel for Sales reports and would like to be able to show both the table and the chart together.Restrict Users access to login into CRM?
I’m wanting my employees to be able to utilize the Zoho CRM Lookup field within Zoho Forms. For them to use lookup field in Zoho Forms it is my understanding that they need to be licensed for Forms and the CRM. However, I don’t want them to be able tohow to change the page signers see after signing a document in zoho sign
Hello, How can I please change the page a signer sees after signing a document in Zoho Sign? I cannot seem to find it. As it is now, it shows a default landing page "return to Zoho Sign Home". Thanks!Holidays - Cannot Enter Two Holidays on Same Day
I have a fairly common setup, where part-time employees receive 1/2 day's pay on a holiday and full-time employees receive a full day's pay. Historically, I've been able to accommodate this by entering two separate holidays, one that covers full-timeIntroducing Connected Records to bring business context to every aspect of your work in Zoho CRM for Everyone
Hello Everyone, We are excited to unveil phase one of a powerful enhancement to CRM for Everyone - Connected Records, available only in CRM's Nextgen UI. With CRM for Everyone, businesses can onboard all customer-facing teams onto the CRM platform toCan a project be cloned?
Good afternoon, greetings. I would like to ask if it's possible to clone a project in Microsoft Project. I found a way to do it using templates, but I'm not sure if there's a direct way to clone a project. Thank you in advance for your attention, andJWT Token authentication problem that sometimes generates infinite redirect loops
Description : Nous proposons un bouton sur notre plateforme permettant de rediriger l'utilisateur vers le portail ZohoDesk via un jeton JWT pour une authentification transparente. Cependant, il arrive que certains utilisateurs soient pris dans une boucleUnknown table or alias 'A1'
I would like to create a subquery but i am getting the following error: Unknown table or alias 'A1' used in select query. This is the sql statement: SELECT A1.active_paying_customers, A1.active_trial_customers, A1.new_paying_signup, date(A1.date_active_customers),Why Sharing Rules do Not support relative date comparison???
I am creating a Sharing Rule and simply want to share where "Last Day of Coverage" (Date field) is Greater than TODAY (Starting Tomorrow). However, sharing rules don't have the option to compare a date field to a relative date (like today), only to Staticin the Zoho creator i have address field based the customer lookup im selecting the addresss , some times the customer address getting as null i want to show as blank
in the Zoho creator i have address field based the customer lookup im selecting the addresss , some times the customer address getting as null ,i want to show as blank instead of showing null. input.Billing_Address.address_line_1 = ifNUll(input.Customers_Name.Address.address_line_1,"");Question about upgrade and storage space Zoho Notebook
After upgarding my Zoho Notebook plan, I am running into the following issue. I just upgraded from a free Zoho Notebook subscription to Pro Lite after I got a notification in my Window Zoho Notebook desktop app saying that I had run out of space. However,how to add email to existing organization i w
I am already registered my organization and i have an email id. I need one more email id but i can't find anywhere .i want the cheapest email id . how to add ?Add multiple users to a task
When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesmanadd zoho account
How to add a zoho mail to previous zoho account? I have twoThe reason I switched away from Zoho Notebook
My main reason for switching to Zoho was driven by three core principles: moving away from US-based products, keeping my data within India as much as possible, and supporting Indian companies. With that intent, I’ve been actively de-Googling my digitalName changed in settings for mailbox but still not changed when typed in To field
In the email account secretary@ i have updaetd the new staff members details but the old members name still appears when I type secretary@ in the To field. I cant work out where Zoho is finding the old name from. I have deleted the browser cache. If IPrinting to a brother label maker
I see allot of really old unanswered posts asking how to print to a label maker from a zoho creator app. Has their been any progress on providing the capability to create a customized height & width page or print template or whatever to print labels?Sync desktop folders instantly with WorkDrive TrueSync (Beta)
Keeping your important files backed up and accessible has never been easier! With WorkDrive desktop app (TrueSync), you can now automatically sync specific desktop folders to WorkDrive Web, ensuring seamless, real-time updates across devices. Important:CRM project association via deluge
I have created a workflow in my Zoho CRM for closing a deal. Part of this workflow leverages a deluge script to create a project for our delivery team. Creating the project works great however, after or during the project creation, I would like to associateTrack online, in-office, and client location meetings separately with the new meeting venue option
Hello everyone! We’re excited to announce meeting enhancements in Zoho CRM that bring more clarity and structure to how meetings are categorized. You can now specify the meeting venue to clearly indicate whether a meeting is being held online, at theCalling the new 'Custom API' feature from within a Custom Widget
From what I've learned it is not possible to call an endpoint from the new "Custom API" feature within a Creator Widget. The SDK's doesn't support it yet, when calling it natively you end up with CORS issues or at least I couldn't get it working evenAnnouncing new features in Trident for Mac (1.32.0)
Hello everyone! We’re excited to introduce the latest updates to Trident, which are designed to reinforce email security and protect your inbox from evolving threats. Let’s take a quick look at what’s new. Deliver quarantined emails. Organization adminsZoho LandingPage is integrated with Zoho One!
Greetings to the Zoho One users out there! We're delighted to let you know that Zoho LandingPage is available in Zoho One too! With Zoho LandingPage, you can host custom-made landing pages, and persuade the visitors to dive deeper by making further clicks,Marketing Tip #5: Improve store speed with optimized images
Slow-loading websites can turn visitors away. One of the biggest culprits? Large, uncompressed images. By optimizing your images, your store loads faster and creates a smoother shopping experience leading to higher sales. It also indirectly improves SEO.PDF Attachment Option for Service Reports
Hello Team, I would like to check with you all if there is an option to attach PDF documents to the service reports. When I try to attach a file, the system only allows the following formats: JPEG, JPG, and PNG. Could you please confirm whether PDF attachmentsZOHO Work Drive Back Up
I am looking for a ZOHO Work Drive backup solution. Something that is cloud based. There's lots of these kinds of options for Google Drive and other providers, but I have not seen anything for WorkDrive. Any suggestions?Wrapping up 2025 on a high note: CRM Release Highlights of the year
Dear Customers, 2025 was an eventful year for us at Zoho CRM. We’ve had releases of all sizes and impact, and we are excited to look back, break it down, and rediscover them with you! Before we rewind—we’d like to take a minute and sincerely thank youIssue with Zoho Creator Form Full-Screen View in CRM Related List Integration
Hi Team, We have created a custom application in Zoho Creator and integrated it into Zoho CRM as a related list under the Vendor module, which we have renamed as Consignors. Within the Creator application, there is a form named “Pickup Request.” InsideIntroducing workflow automation for the Products module
Greetings, I hope all of you are doing well. We're happy to announce a few recent enhancements we've made to Bigin's Products module. The Products module in Bigin now supports Workflows, enabling you to automate routine actions. Along with this update,Zoho Browser??
hai guys, this sounds awkward but can v get a ZOHO BROWSER same as zoho writer, etc. where i can browse websites @ home and continue browsing the same websites @ my office, as v have the option in Firefox, once i save and close the browser and again when i open it i will be getting the same sites. If u people r not clear with my explanation, plz let me know. Thanks, SandeepTask/Activity indicator in SalesPipeline overview has disappeared
I Just logged in my ZOHO CRM first 2026 checking my salespipeline overview , Every record card used to show an indication that there was an open task (Yellow if the expiry date was close, red if the expiry date was today and grey when it had expired).Notes badge as a quick action in the list view
Hello all, We are introducing the Notes badge in the list view of all modules as a quick action you can perform for each record, in addition to the existing Activity badge. With this enhancement, users will have quick visibility into the notes associatedSMS to customers from within Bigin
Hi All, Is there anyone else crying out for Bigin SMS capability to send an SMS to customers directly from the Bigin interface? We have inbuilt telephony already with call recordings which works well. What's lacking is the ability to send and receiveAdmins cannot see each others' Scheduled Reports?!
Very frustrating that as an admin I cannot see what my reports my fellow admins have created and scheduled. After asking about this on the help chat, I was told the issue is trust and security. By giving someone Admin status, it means we trust them with those responsibilities. Please change this, it is not a good process to have to bother other users to change a report or change users within a report.Automatically CC an address using Zoho CRM Email Templates
Hi all - have searched but can't see a definitive answer. We have built multiple email templates in CRM. Every time we send this we want it to CC a particular address (the same address for every email sent) so that it populates the reply back into ourWriter update results in BitDefender blocking it as malware
After updating Writer to latest update, Bitdefender blocked the app and writer no longer runs.Next Page