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
www.abc.com is working but abc.com is not
I have my domain in godaddy. I have created a website using zoho sites and now I want to publish it and I tried doing it. Mapped the domain www.abc.com, abc.com. In godaddy changed the CNAME values. A, @ was parked and updated with zoho's IP address.Split my account
Hello, I recently bought an email service for a friend. But, looks like I used my personal account to buy the plan. Please split the account so that the email service stays in a different account than my personal account.Have Mail Lite, wish to add/purchase 2nd domain and use email
Hello, I have recently created two new businesses. I have successfully used Zoho to create a domain for one business and have set up two email addresses / users on that domain. I am trying to figure out how to create a second domain, just like I did theNeed support in setting up the company email and few other setups
Need support in setting up the company email and few other setupsThis mobile number has been marked spam.
Dear zoho team, I am very happy to use zoho,because it india 🇮🇳 own app, but when we login into zoho account we facing some issue, please solve below issue ASAP. Add your mobile number As an added security measure for Accounts, you need to verify andAdd an external email to the allowed list
I need to allow an external email to raise a ticket on Manage Engine ServiceDesk Plus. The external email is a outside of our domain and is not a domain user. The email address that needs to be allowed to raise a ticket request is noreply@formsatack.com.Display name & account type change
I made a mistake and subscribed with an individual account instead of business, how can I change the account type and enter the details of the business. The other question, if I can't, then I want the display name of our email to be 'Business name' insteadRequest to change display name in Zoho mail
Hello Zoho Mail Support, I am experiencing an issue with changing the display name on my Zoho Mail account. My email address is: info@witchcraftproduction.rs . I have already updated the display name in Settings > Mail Accounts, where it now shows asOutbound IP address SPAM problem.
Hello Zoho Support, We noticed that one of your outbound IP addresses (136.143.188.12), which delivers our domain’s mail, is currently listed on the UCEProtect blacklist. Because of this, our messages are sometimes flagged as spam by recipient servers.Answer Bot and Personalized Questions
Hi there, I have the same problem using the SalesIQ Answer Bot and the Zoho Desk Answer Bot (which really need different names, to be honest, in order to avoid confusion...) Customers that visit our website ask questions in the form of "What do you do?"Forwarding
How can I forward my Zoho account to my gmail account? NoëlAdd a Way to Duplicate Cards in the Same Canvas (Retain All Settings)
Dear Zoho SalesIQ Team, We would like to request a new feature in Zoho SalesIQ: the ability to duplicate an existing card within the same canvas while retaining all of its information and settings, including conditions, configurations, and display preferences.Zoho Mail CC and BCC not working
Zoho Mail CC and BCC not working. I have tried multiple combinations amongst my domain aliases but emails sent as CC or BCC never arrive, not even to Spam. I have checked that the DNS is set up correctly in Cloudflare and have even received a DMARC reportZoho Autoplay Issue
Currently, we run our graphics on a playlist with youtube videos interspersed in our office -- however, when we go to update the playlist (since last Thursday at around 8-9am EST) it no longer saves the autoplay function. We have tried making a new account, manually saving with cntrl+s, making a new show, etc but nothing seems to work. Even with our old program, we can't change it off of autoplay for ones that were created in the past. Another issue is that if we change a slide transition to anythingCritical Issue: Tickets Opened for Zoho Support via the Zoho Help Portal Were Not Processed
Hi everyone, We want to bring to your attention a serious issue we’ve experienced with the Zoho support Help Portal. For more than a week, tickets submitted directly via the Help Portal were not being handled at all. At the same time no alert was postedAdding hyperlinks in CRM emails time automatically
It may just be me, but when I am writing an email to a lead, I find inserting a hyperlink very time consuming. Granted, I can use templates but there are a ton of scenarios where I might want to put a link in to an website that wouldnt require me to go though the effort of creating a template. Ideally, the crm would identify that I that a string of text is a URL and insert the hyperlink automatically, just like microsoft outlook or gmail. Has anyone else had this same experience and found a wayEnhance "Applications Usage" with Date Filters, Historical Analytics & App-Level Breakdown
Hello Zoho Creator Team, We are writing to request a critical enhancement to the Applications Usage section to improve our ability to monitor, analyze, and manage our platform consumption over time. While the current view of today’s usage is helpful forExternal File Share - Allow delete
Hi Team, when I share an external link and give it edit rights the external user can add but not delete files and folders. what am i doing wrong?How to notify all members on any updates to zoho crm?
Hi, I am using the free version of zoho CRM and currently seeing this will work for our company. We are a small company and wanted to be more informed about all the changes in zoho. 1. How do I s et notifications that go to the team for any and all changesHow to change the format for phone numbers?
Mobile phone numbers are currently formatted (###) ###-####. How can I change this to a more appropriate forms for Australia being either #### ### ### or (#)### ### ###?Unattended Access on Android without Play Store
I'm testing Zoho Assist for remote config and maintenance of our IoT devices. The devices are running Android 8.1 and do NOT have Google Play Store installed, nor can it be installed. I've been able to install Zoho Assist on the devices and load the enrollmentOpen Sans Font in Zoho Books is not Open Sans.
Font choice in customising PDF Templates is very limited, we cannot upload custom fonts, and to make things worse, the font names are not accurate. I selected Open Sans, and thought the system was bugging, but no, Open Sans is not Open Sans. The realIs it possible to embed Zoho Bookmarks in the Cliq sidebar?
Is there any way that each Zoho user can access their bookmarks (that live in https://bookmarks.zoho.eu/ which is technically a part of Zoho Mail) directly within Cliq? As a widget, or an item in the sidebar? My team does not use Mail, it uses Cliq allShow Attachments in the customer portal
Hi, is it possible to show the Attachments list in the portal for the particular module? Bests.Kaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Navigate with Ease: Announcing Improvements to Your Zoho CRM for Everyone's Setup Experience
Hello Everyone, We’re thrilled to announce new enhancements to the Setup Menu in our Zoho CRM for Everyone system, designed to simplify your workday and streamline your overall experience. What's New? Addition of a Setup Homepage Faster Search in SetupZoho Projects Webhook fails with HTTP Error 0
Hello Zoho Community, I am pulling my hair out over this one. I have setup a very basic http(s) server that always responds "ok" and code 200 to incoming GET requests. It will accept any parameters, and any path. Really, all it does is say "ok," and logZOHO Campaignで表のカラムの幅を調整したい。
表を作成した際、個々のカラムの幅を調整したいのですが、方法が分かりません。 どなたかご存じの方ご教示ください。Auto-upload Creator Files to WorkDrive
Hi everyone, I’m working on a workflow that uploads files from Zoho Creator to specific subfolders in Zoho WorkDrive, as illustrated in the attached diagram. My Creator application form has two multi-file upload fields, and I want—on successful form submission—toExciting Updates to the Kiosk Studio Feature in Zoho CRM!
Hello Everyone, We are here again with a series of new enhancements to Kiosk Studio, designed to elevate your experience and bring even greater efficiency to your business processes. These updates build upon our ongoing commitment to making Kiosk a powerfulKaizen #129 : Client Script Support for Blueprints
Hello everyone! Welcome to another week of Kaizen. Today, let us discuss about how you can use Client Script during a Blueprint transtion to meet your requirements. This Kaizen post will provide solution for the post - Need non-mandatory fields in blueprintSearch Bar Improvement for Zoho Commerce
Hey everyone, I've been using Zoho Commerce for a bit now, and I think the search bar could really use an upgrade. Right now, it doesn't show products in a dropdown as you type, which would make finding items a lot faster. On Shopify, for example, youMaking digital signatures accessible to all: Introducing accessibility controls in Zoho Sign
Hi there! At Zoho Sign, we are committed to building an inclusive digital experience for all our users. As part of our ongoing efforts to align with Web Content Accessibility Guidelines (WCAG), we’re updating the application with support that will goAccount Owner Field From Accounts Module to be Displayed in Contacts module
I have a field in the Accounts Module in the CRM called "Account Owner" i want that field to be also mapped into the Contacts Module custom single line field called "Account Manager".Update a field in the ZOHO Form, basis numeric value in another field in the same form
I am trying to create a questionnaire in ZOHO, where clients need to answer 10 questions, and basis response, values are assigned. I have created a total score field where the sum of the values is stored. But i am unable to create a rule whereby anotherHow to update "Lead Status" to more than 100 records
Hello Zoho CRM, How do I update "Lead Status" to more than 100 records at once? To give you a background, these leads were uploaded or Imported at once but the lead status record was incorrectly chosen. So since there was a way to quickly add records in the system no matter how many they are, we are also wondering if there is a quicker way to update these records to the correct "Lead Status". I hope our concern makes sense and that there will be a fix for it. All the best, JonathanMeet up de Zoho en Bilbao
Buenos días comunidad! Estamos estudiando hacer un Meet up en Bilbao desde zoho y varios Partners. Para que la experiencia sea excelente, queremos saber cuantas pesonas se vendrían a Bilbao al evento. Y para que sea lo mas útil posible, que temas dentroPicklist reference value in REST
picklist options can be configured to have a different reference value than the displayed one, should be helpful in things like multilanguage: https://help.zoho.com/portal/en/kb/crm/customize-crm-account/translations/articles/translations is there a wayIn Zoho inventory Converting sales return to cerdit note from using Api from Creator Error details: {"code":-1,"message":"Invalid Sales Return ID."}
In Zoho inventory Converting sales return to cerdit note from using Api from Creator Error details: {"code":-1,"message":"Invalid Sales Return ID."} this is button Function used in the Creator map Inventory.Create_Credit_note(int CRE_ID) { return_valueMarketing Tip #2: Recover lost sales with abandoned cart emails
Did you know most online shoppers don’t complete checkout? Automated cart recovery emails are an easy way to bring them back. A simple reminder can recover sales you’d otherwise lose. Try this today: Enable abandoned cart emails in Zoho Commerce and setNext Page