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
Zoho Sheets saying locked when not locked
Zoho Sheets won't let me add more rows to sheet because it's saying the sheet is locked, but the sheet is not locked. I tried using a different browser but I still have the same issue.Identifying and Merging Accounts with Similar (Non-Exact) Names
Hello everyone, I’m aware of the built-in deduplication feature in Zoho CRM that allows merging records with exactly matching values for selected fields. However, I’m running into a situation where our migrated data contains multiple variations of theOption for super admin to recover the deleted chats in Zoho Cliq
Currently, in Zoho Cliq, if a user accidentally deletes their chats, there is no option for the Super Admin to recover or view those deleted messages. I believe it would be helpful if Super Admins had a recovery option, perhaps within a certain time frame,Zoho CRM iOS app updates: the Homepage, Zoho Survey integration, and support for multiple file uploads in subform
Hello everyone! The Zoho CRM mobile app continues to evolve to bring you a smoother and more powerful CRM-on-the-go experience. We're excited to share some important updates now available in the iOS app. Here's what's new: Homepage support Zoho SurveyI am trying to integrate my module in sandbox environment to add data via API
I went through the documents and tried to setup the API for adding the module via the api but the problem is the token setup is done on "https://accounts.zoho.in/oauth/v2/token" domain and the bulk write is done on "https://content.zohoapis.com/crm/v8/upload"Setting up primary mobile number.
Hi Sir Earlier I joined IIFL team and got an organizational email of zoho services. Later i quit and now my account is deactivated with IIFL but i am unable to link my mobile number to my own email id with zoho.Zoho sheet for desktop
Hi is zoho sheets available for desktop version for windowsTables for Europe Datacenter customers?
It's been over a year now for the launch of Zoho Tables - and still not available für EU DC customers. When will it be available?Guided Conversations - Ticket Creation
Hi there, Using Guided Conversations to Take Customer Data and apply it into a Support Ticket for internal use, Is there a way to take multiple Textual Variables Inputs (A series of questions), and have the answers all appear in the Description of theHow to send certain invoices for Approval based on condition?
To streamline operations, I need the invoices to go for approval when certain conditions are met. For example, if my staff gives a discount of x amount, it must go for approval. If there is no discount, then the invoice will be approved automatically.Unable to continue payment
Hi, I want to make a payment for my email subscription, but I cannot proceed with the payment because my NITKU is invalid. In Indonesia, the NITKU consists of 22 digits, but Zoho only provides 20 digits, causing my NITKU to be invalid. Please help meIs there a way to show contact emails in the Account?
I know I can see the emails I have sent and received on a Contact detail view, but I want to be able to see all the emails that have been sent and received between all an Accounts Contacts on the Account Detail view. That way when I see the Account detailMassive spam pretending to come from our address – is this a Zoho security issue?
Hi, We’ve been receiving more and more suspicious emails lately — many of them clearly look like scams. But yesterday, we got an email that appeared to be sent from our own address, which was very concerning. We're starting to wonder if this might beCant recieve mails
I’m having an issue with my external IMAP account not fetching any emails into Zoho Mail, even though the connection test says “Success”. I can send, if someone sends an email to me. my original directory (home.pl host) recieves it in their portal butZoho Sheet Autofit Data
While using Autofit Data function in Zoho Sheets with Devnagri Maratji or Hindi Fonts, a word or a number, it keeps couple of characters outside the right side border.Enhancements to finance suite integrations
Update on the migration (October 28, 2025): Starting this week (October 29 tentatively), we’ll begin migrating Zoho CRM users to Zoho Books as integration users. This means actions will now run under each user’s own Zoho Books access, instead of the admin’s.Edit 'my' Notes only
The permissions around Notes should be more granular, and allow to user to be able to edit the notes he created only. The edit Notes permission is useful as it allows the user to correct any mistakes or add information as needed. However, with this sameHow do I filter contacts by account parameters?
Need to filter a contact view according to account parameter, eg account type. Without this filter users are overwhelmed with irrelevant contacts. Workaround is to create a custom 'Contact Type' field but this unbearable duplicity as the information alreadyJob Alerts for Candidates
hi All, I have 2 questions relating to sharing job details with candidates. 1. is there a way to notify candidates that meet certain criteria of current jobs available? eg. I run a candidate search, and identify 50 candidates that might be suitable. CanAdd 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 salesmanEnhancing Task Accuracy: Automatically notify the owners upon Task duplication.
Hello Everyone, A Custom function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. The requirementShow/ hide specific field based on user
Can someone please help me with a client script to achieve the following? I've already tried a couple of different scripts I've found on here (updating to match my details etc...) but none of them seem to work. No errors flagged in the codes, it justWhere are Kanban swimlanes
So i've been playing with Zoho Projects Kanban view a bit more. It appears that task lists are being used as the Kanban columns, which makes sense from the implementation point of view but not the logical one. Kanban columns are statuses that a task can flow through, while a task list has been a logical way to organize related tasks and relate them to a mislestone. In other words a task in a particular task can go through several stages while remaining in the same task list. After doing some researchTip 19: How to display images in Pages using ZML snippets
Hi folks, We're back with another tip on how to use Zoho Markup Language (ZML) to create interactive pages in Zoho Creator. You can use ZML snippets to add various kinds of components to your pages and structure them in meaningful ways. That's not all, though—using ZML you can ensure your application is automatically compatible with all kinds of devices without any inconsistencies. We often get questions from users about how to invoke Deluge scripts within ZML code. Here's a simple use case for addingCan zoho swign format block text spacing automatically when prefilled from zoho crm?
I'm sending zoho sign template from zoho crm, so that zoho crm pre-populates most fields. I have existing pdf documents that i am having signed. I have no ability to change the pdf documents, they are standardized government forms. The problem I am facingZoho Inventory / Finance Suite - Add feature to prevent duplicate values in Item Unit field
I've noticed that a client has 2 values the same in the Unit field on edit/create Items. This surprised me as why would you have 2 units with the same name. Please consider adding a feature which prevents this as it seems to serve no purpose.Reference lookup field values in Client script
hello all, I'm using a "ZDK.Apps.CRM.Products.searchByCriteria" function call, which is returning the matching records correctly; however, one of the fields is a lookup field which I need the associated data. I believe there is a way to get this dataIn App Auto Refresh/Update Features
Hi, I am trying to use Zoho Creator for Restaurant management. While using the android apps, I reliased the apps would not auto refresh if there is new entries i.e new kitchen order ticket (KOT) from other users. The apps does received notification but would not auto refresh, users required to refresh the apps manually in order to see the new KOT in the apps. I am wondering why this features is not implemented? Or is this feature being considered to be implemented in the future? With theEvery time an event is updated, all participants receive an update email. How can I deactivate this?
Every time an event is updated in Zoho CRM (e.g. change description, link to Lead) every participant of this meeting gets an update email. Another customer noticed this problem years ago in the Japanese community: https://help.zoho.com/portal/ja/community/topic/any-time-an-event-is-updated-on-zohocrm-calendar-it-sends-multiple-invites-to-the-participants-how-do-i-stop-that-from-happeningHow can I filter a field integration?
Hi, I have a field integration from CRM "Products" in a form, and I have three product Categories in CRM. I only need to see Products of a category. Thanks for you answers.How to implement new online payment gateway?
Hello, Can you tell me how to proceed to implement my local payment gateway? DIBS has an open avaiable API that should be easy to implement into ZOHO BOOKS. http://tech.dibspayment.com/dibs_payment_windowZoho CRM - Portal Users Edit Their Own Account Information
Hi Community, I'm working on a client portal and it seems like the only I can make the Account record editable to the Contact, is if I add another lookup on the Account to the Contact record. Am I missing something as the account already has a list ofI’ve noticed that Zoho Sheet currently doesn’t have a feature similar to the QUERY formula in Google Sheets or Power Query in Microsoft Excel.
These tools are extremely helpful for: Filtering and extracting data using simple SQL-like queries Combining or transforming data from multiple sheets or tables Creating dynamic reports without using complex formulas Having a Query-like function in ZohoConnecting Zoho Mail with Apollo.io
Hi, I am trying to connect my Zoho Mail account with my Apollo.io account to start sending cold email for prospecting purposes. I have activated the IMAP setting but I am still unable to connect to the Apollo account. I am using my email credentials butWhere does this report come from in the Zoho One ecosystems?
Is this directly from MA, Analytics or ??? ???Contact's title in "Contact Role Mapping"
When I'm creating a deal, I'd like to see the contacts title in the listing. Right now, I only see this: How can I get the contact's title in there?Zoho CRM - Client Portal - Hide Notes Related List
Hi Community, I'm building a customer portal and I can't find a way to hide the notes related list. I don't want the client to see the notes I have about them. Is there a way to do this as it is no bin/trash icon when I hover over."Pivot Table" Conditional Formatting
Team, I there a way to use conditional formatting a "Pivot Table" report? Thanks, Arron Blue Pumpkin Hosting | Solutions Made SimpleHow many clients can be added to Zoho Practice?
How many clients can be added to Zoho Practice without having their zoho app?Stage History
when viewing a ticket , and you look at stage history tab (kanban view) and look at the stage duration column in days, it shows the current stage of the ticket as " current stage ". Should it not rather show the amount of days it has been in that currentNext Page