I created a material planning report that shows the raw material needs by week. In this report, we can see how much material is required for each week. However, while we have many raw materials in stock, we don’t know when they will run out.
I would like to see the cumulative stock status. For example, if we need 100 meters of fabric but only have 85 meters in stock:
In the first week, we need 55 meters.
In the second week, we need 20 meters.
In the third week, we need 25 meters.
By the third week, the fabric runs out. Is there a way to determine automatically which week the stock will be depleted?
Hi! To address this issue in your Qlik material planning report, you can use a cumulative calculation to track stock levels and determine when the stock will run out.
Ensure you have the following fields in your dataset:
Material: Identifier for the raw material. Week: Week number or date. Stock: Initial stock available (can be static or dynamic). Need: Amount required for that material in the given week. Calculate Running Balance in the Script: You can calculate the running balance using Qlik's scripting capabilities.
LOAD Material, Week, Need, Stock FROM [YourDataSource];
// Create a running balance RunningBalance: LOAD Material, Week, Stock - RangeSum(Above(Sum(Need), 0, RowNo())) AS RemainingStock RESIDENT YourDataSource ORDER BY Material, Week;
This will calculate the stock after subtracting the cumulative "Need" from the initial "Stock."
Add a calculated column in your app to flag when the stock goes negative.
IF(RemainingStock < 0, 'Depleted', 'Available') AS StockStatus Alternatively, you can calculate the week of depletion directly: IF(RemainingStock < 0 AND Above(RemainingStock) >= 0, Week) AS DepletionWeek Create Visualizations in this way use a line or bar chart to visualize the stock over time. Add a calculated field to show the depletion week or flag it in a table.
Example Output:
Material Week Need Stock RemainingStock DepletionWeek Fabric 1 55 85 30 Fabric 2 20 85 10 Fabric 3 25 85 -15 3 If you want users to adjust the stock interactively, create an input box where they can input or modify the initial stock value. Link this value to your calculation.
Visualization in Qlik Sense: Table View: Show cumulative "RemainingStock" alongside "Need" and "Week." Chart View: Use a line chart to plot RemainingStock over Week to highlight where it crosses below zero. With this approach, you'll be able to automatically identify the week when stock will be depleted for each raw material and plan accordingly!
