To find the Closing_Stock @ month end

harleen_singh — Mon, 17 Oct 2011 09:16:11 GMT

Hi,

I am working on inventory module, inwhich three table are there

1. Inventory

2. TRN_Receipt

3. TRN_Issue

In Inventory table we have Item wise Opening_Amt, Opening_Qty and only one date as '01-April-11' ( Basically its 1stApril Opening )

In TRN_Receipt we have Item wise Receive_Qty, Receive_Amt and Doc_Date(Receive Date)

In TRN_Issue we have Item wise Issue_Qty , Issue_Amt and Doc_Date(Issue Date)

and make Doc_Date asCommon_date (picking date from Inventory,TRN_Receipt, TRN_Issue)

and Item from Invetory, TRN_Receipt,TRN_Issue as Common_Item

i want to calculate inventory @month_end

Formule for Closing_Qty =Opening_Qty + Receive_Qty - Issue_Qty

For month = 'April'

ClosingQty = Opening_Qty of April + Receive_Qty of April - Issue_Qty ofApril

For month ='May' Opening Qty = 'Closing Qty of April'

So For Closing_Qty for May = 'Closing Qty of April ' + Receive_Qty of May - Issue_Qty of May

and so on

Exp

Inventory Table :- Date Item Opening_Qty

1/Apr/2011 A 100

1/Apr/2011 B 20

1/Apr/2011 C 10

Receive Table

Doc_Date Item Receive_Qty

2/Apr/2011 A 10

3/Apr/2011 A 25

21/May/2011 A 100

IssueTable Doc_Date Item Issue_Qty

1/Apr/2011 A 5

21/Apr/2011 A 8

28/May/2011 A 80

Output will be

Month Item Opening_Qty Closing_Qty

Apr11 A 100 122

Apr11 B 20 20

Apr11 C 10 10

May11 A 122 142

May11 B 20 20

May11 C 10 10

Please help me to find the correctoutput

Regards

Re: To find the Closing_Stock @ month end

tanelry — Mon, 17 Oct 2011 10:41:39 GMT

In my opinion the best way is to build the data model where opening inventory and all transactions (receipts and issuing) form the fact table (concatenated),

then connect calendar table to transaction dates indirectly, through a link table so that every single calendar date corresponds to all preceding transaction dates.

As a result you can build charts to calculate closing Qty at any calendar date/week/month simply with sum(Qty).

This is how the tables could look like:

Transactions:

StockEvent TransDateID Item Qty

Start 20110401 A 100

Receive 20110402 A 10

Issue 20110401 A -5 (note: negative qty for issue)

DateLink:

TransDateID DateID

20110401 20110401

20110401 20110402

20110402 20110402

20110401 20110403

20110402 20110403

20110403 20110403

Calendar:

DateID, Date, Year, Month, Week ...

Re: To find the Closing_Stock @ month end

SunilChauhan — Mon, 17 Oct 2011 11:04:43 GMT

see the attached file

please apply on your actual data

hope this helps

topic Re: To find the Closing_Stock @ month end in QlikView

To find the Closing_Stock @ month end

Re: To find the Closing_Stock @ month end

Re: To find the Closing_Stock @ month end