topic How to get the ending inventory in QlikView

How to get the ending inventory

Mon, 26 Jan 2026 21:26:37 GMT

Hi,

I have a problem in getting the ending inventory report could you pleas help me on what to do. See below sample for reference.

BWART as MovementType,
if(BWART = '561', 'BegBal') as Beg_Balance,
if(BWART = '562', 'BegBalReversal') as Beg_Bal_Reversal,
if(BWART = '351', 'GoodIssuePlant') as Good_Issue_Plant,
if(BWART = '301', 'GoodsIssue') as Goods_Issue,
if(BWART = '302', 'GIReversal') as GI_Reversal,
if(BWART = '901', 'GIStockTrfPlant') as GI_Transfer_Plant,
if(BWART = '902', 'GIStockTrfPlantReversal') as GI_TPlant_Reversal,
if(BWART = '101', 'GoodsReceipt') as Goods_Receipt,
if(BWART = '102', 'GRReversal') as GR_Reversal,
if(BWART = '951', 'Sales') as Sales,

MENGE as QTY,
if(BWART = '561', MENGE, 0) as BegInv,
if(BWART = '101', MENGE, if(BWART = '302', MENGE, 0)) as Receipt,
if(BWART = '351', MENGE, if(BWART = '301', MENGE,
if(BWART = '901', MENGE, if(BWART = '102', MENGE,
if(BWART = '951', MENGE, 0))))) as Issuance,

This will be the correct output for Summary Report :
BegInventroy Receipt Issuance EndInventory

5 2 2 5
10 6 4 12
20 10 10 20

Thanks,

Jhon

How to get the ending inventory

suniljain — Thu, 03 Mar 2011 05:15:54 GMT

Standard Logic is

Opening Stock + Receipt Qty - Issue Qty = As On Date Stock

You are doing this in MSEG

to built this logic in SAP you have to cumulate receipe and issue based on requirement .

and you have to avoid 309 movement.

How to get the ending inventory

Thu, 03 Mar 2011 07:15:14 GMT

Hi Sunil,

I am using this script (<BegInv> + <Receipt> - <Issuance>) as EndInv, to get the ending inventory but unfortunately I have encounter an error.I hope u could help me to resolve my problem.

Thanks,

Jhon

How to get the ending inventory

suniljain — Fri, 04 Mar 2011 07:07:43 GMT

Can you post your data in Excel.?.

How to get the ending inventory

Fri, 04 Mar 2011 07:45:43 GMT

Hi Sunil,

Here is my sample data. I hope you could help me to get the ending inventory.

Thanks,

Jhon

Branch_Code	Mat_Number	Movement_Type	StockQTY	BegInv	Beg_Balance	Beg_Bal_Reversal	Receipt	Goods_Receipt	GR_Reversal	Goods_Issue	GI_Reversal	Issuance	Sales	Should be EndInventory
5	200058	BegBal	1	1	BegBal		0					0		1
5	200058	BegBal	13	13	BegBal		0					0		13
5	200058	GoodsReceipt	24	0			24	GoodsReceipt				24		0
5	200058	Sales	1	0			0					1	Sales	0
6	200058	BegBal	19	19	BegBal		0					0		19
6	200058	GoodsReceipt	24	0			24	GoodsReceipt				24		0
6	200058	GoodsReceipt	48	0			48	GoodsReceipt				48		0
6	200058	Sales	1	0			0					1	Sales	0
27	200058	BegBal	12	12	BegBal		0					0
27	200058	Sales	1	0			0					1	Sales
31	200058	BegBal	17	17	BegBal		0					0
31	200058	GoodsReceipt	24	0			24	GoodsReceipt				24
31	200058	Sales	1	0			0					1	Sales
221	200058	GoodsReceipt	24	0			24	GoodsReceipt				24
221	200058	Sales	1	0			0					1	Sales
222	200058	GoodsReceipt	24	0			24	GoodsReceipt				24
222	200058	Sales	1	0			0					1	Sales
222	200058		1	0			0					0
223	200058	BegBal	1	1	BegBal		0					0
223	200058	BegBal	3	3	BegBal		0					0
223	200058	BegBalReversal	3	0		BegBalReversal	0					0

How to get the ending inventory

Fri, 04 Mar 2011 09:05:11 GMT

Hi Sunil,

Please download attachedment. I hope u would find the way to help me.

Thanks,

Jhon

How to get the ending inventory

suniljain — Tue, 08 Mar 2011 12:05:54 GMT

Following is the logic of FIFO Inventory Method.

Stock_Summary:
Load distinct

MATERIAL,
PLANT,
Storage_Location,
M_Date ,
SUM(Quantity_Received_Into_Valuated_Stock - Quantity_Issued_From_Valuated_Stock) as Rec_Issue_Valuated_Stock,
SUM(Receipt_Quantity_Blocked_Stock - Issue_Quantity_Blocked_Stock) as Rec_Issue_Blocked_Stock,
SUM(Quantity_Received_Into_Consignment_Stock - Quantity_Issued_From_Consignment_Stock) as Rec_Issue_Consignment_Stock,
SUM(Receipt_Quantity_Stock_In_Quality_Inspection - Issue_Quantity_Stock_In_Quality_Inspection) as Rec_Issue_Quality_Inspection_Stock,
//SUM(Issue_Quantity_Stock_In_Transit- Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock,
SUM(Value_Received_Into_Valuated_Stock - Value_Issued_From_Valuated_Stock) AS Rec_Issue_Value_Valuated_Stock ,
SUM(Receipt_Quantity_Stock_In_Transit) as Rec_Issue_Intransit_Stock

resident Stock_Master
group by PLANT,Storage_Location, MATERIAL,M_Date ;
drop table Stock_Master;

Stock_Summary_Index:
load
*,
recno() as d
resident Stock_Summary order by PLANT,Storage_Location, MATERIAL, M_Date;
drop table Stock_Summary;

Material_Stock:
Load
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)+Rec_Issue_Valuated_Stock,Rec_Issue_Valuated_Stock) as Total_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Valuated_Stock)) as Opening_Valuated_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Blocked_Stock)+Rec_Issue_Blocked_Stock, Rec_Issue_Blocked_Stock) as Total_Blocked_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Consignment_Stock)+Rec_Issue_Consignment_Stock, Rec_Issue_Consignment_Stock) as Total_Consignment_Stock,
if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Rec_Issue_Intransit_Stock) as Total_Intransit_Stock,

if(Peek(MATERIAL) = MATERIAL and Peek(PLANT) = PLANT and Peek(Storage_Location) = Storage_Location ,Peek(Total_Value_Valuated_Stock)+Rec_Issue_Value_Valuated_Stock, Rec_Issue_Value_Valuated_Stock) as Total_Value_Valuated_Stock

resident Stock_Summary_Index order by d;
drop field d;
drop table Stock_Summary_Index;

store Material_Stock into ..\Data\Modelled QVD\Material_Stock.qvd;
Drop Table Material_Stock;

//```````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Stock_Summary:
LOAD
MATERIAL,
PLANT,
Storage_Location,
M_Date ,
Rec_Issue_Valuated_Stock,
Rec_Issue_Intransit_Stock,
Rec_Issue_Blocked_Stock,
Rec_Issue_Consignment_Stock,
Total_Valuated_Stock,
Opening_Valuated_Stock,
Total_Blocked_Stock,
Total_Consignment_Stock,
Total_Intransit_Stock,
Total_Value_Valuated_Stock
FROM
[..\Data\Modelled QVD\Material_Stock.qvd]
(qvd);

RIGHT JOIN (Stock_Summary) LOAD
*,
if( previous(PLANT) = PLANT AND // If the same Product,
previous(Storage_Location) = Storage_Location AND
previous(MATERIAL) = MATERIAL ,
date(previous(M_Date)-1) // Assign the prior day
, '' ) as EndDate // Otherwise assign infinity
RESIDENT Stock_Summary
// The ORDER is important. We must group by Product, and within Product, we must read Descending -- most recent Price first.
ORDER BY PLANT,Storage_Location,MATERIAL, M_Date DESC ;

/*
Determine the lowest date in the entire transaction set.
We will use this to generate the range of dates.
*/
MinDate:
LOAD
num(min(M_Date)) as minAllDate
RESIDENT Stock_Summary
;
LET vStartDate = fieldValue('minAllDate',1)-1; // Put the mindate in a variable for later loop
DROP TABLE MinDate; // No longer need the table

/*
Generate a table of Dates from the minDate up through and including today.
*/
LET vEndDate = num(today(1)); // Generate dates through today

//-----------------------------------------------------------------------------------------------------------
StockMaster: // This will be the new pricing table

LOAD *, month(Date) as Month // Add a Month dimension to ease navigation
;
LOAD
date($(vStartDate) + IterNo(),'DD.MM.YYYY') as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate); //OK

/*
Use IntervalMatch to JOIN PriceMaster and PriceTransaction by StartDate/EndDate range.
*/

LEFT JOIN (StockMaster) IntervalMatch(Date)
LOAD
DISTINCT M_Date,
EndDate
RESIDENT Stock_Summary; //OK

/*
JOIN the Price field to PriceMaster to allow us to drop PriceMaster and
avoid the synthetic key.
*/
LEFT JOIN (StockMaster) LOAD * RESIDENT Stock_Summary; //OK

//------------------------------------------------------------------------------------------------------------

//store StockMaster into ..\Data\Modelled QVD\Summarize_Stock_Master.qvd; //
DROP TABLE Stock_Summary;

// We don't need the StartDate & EndDate fields anymore

How to get the ending inventory

Wed, 09 Mar 2011 08:45:40 GMT

Hi Sunil,

Thank you for the help, I will keep you posted.

Thanks

Jhon

How to get the ending inventory

suniljain — Fri, 11 Mar 2011 10:35:27 GMT

It is done or what ?.

How to get the ending inventory

Mon, 14 Mar 2011 03:51:53 GMT

Hi Sunil,

Sorry for the late response, Yes my concern has been resolved. Thank you so much for your big help, the codes you've provide its really help me to get the Ending Inventory.

Regards,

Jhon

How to get the ending inventory

suniljain — Mon, 14 Mar 2011 16:22:55 GMT

Thanks Lot to giving me opportunity to solve

Re: How to get the ending inventory

Thu, 09 Oct 2014 05:11:42 GMT

HI Sunil,

Need Some help..

Can you check this link

Stock Aging buckets ?

Regards,

Helen

Re: How to get the ending inventory

vikasmahajan — Thu, 09 Oct 2014 05:24:29 GMT

PFA sample hope this may help you

vikas

Re: How to get the ending inventory

Thu, 09 Oct 2014 05:44:27 GMT

HI Vikas,

http://community.qlik.com/thread/136422

Could you please gonthrough the Link ?

Regards,

Helen