Inventory Balances

[#1]

Hi all,

Can someone help to advise how to compute inventory opening and closing balances across month and year.

Thanks & Best Rdgs

Steven

[Oleg_Troyansky]

Steven,

this is not a quick story to tell...

- For starters, I'd recommend combining Sales and Purchases into a single table "Transactions", only differentiating them by a flag or a Qualifier.

- You will also need current inventory balances, that you can use as the "starting point". With the current balance and all the historical transactions to go back in time and compute historical balances.

- It would be overly simplified if all your transactions were covered by Sales and Purchases... You should probably have some other types of Inventory transactions - like, for example, Cycle Counting adjustments, Quality reclassification, etc... Try to get hold of all possible transactions, or you'll never be able to tie to any other sources of information your end users have.

- Once you have the starting point and all the transactions, you need to :

1. Make a distinct list of all Items with their current balances

2. Create Monthly records for each Item - whether or not the item was purchased or sold in a month, you should have a row for eacj Item and each Month - within the desired time frame.

3. Create a monthly summary of all monthly transactions per Item/Month and left-join it into your table with Monthly Records.

4. Reload your Monthly records, sorted by Item and Month (descending), and calculated Monthly Opening Balance for each Item, using the previous balance and adding/subtracting the Monthly transactions. You'll need to use functions peek() and previous() to calculate it.

I know it sounds complex, but once you follow all the steps, it's not that bad. I think it's described in detail within the Developer II class. If you have the class materials, look it up. Contact your QlikView provider to get access to those materials or to go through the class. I think there is an on-line class coming up soon - look it up in the Training section on this website.

cheers,

[suniljain]

I ckeck your application . But For Inventory balance I require more information.

like what is business scenarion to calculate inventory in your case.

Like Cumulative (Purchase-sales) = Closing balance or anything esle.

Regards

Sunil Jain.

[#4]

Hi Sunil Jain

I have purposely ignored the inventory adjustments, cycle count, order commitment etc because I just want to get the correct formula in computing the opening and ending balance. Just to keep it simple, ending = opening + purchase - sales.

Please ensure that the opening and ending is still correct when user select either day, month, year or any combination.

Thanks & Best Rdgs

Steven

[#5]

Hi Oleg Troyansky

I ram happy to hear from you and really appreciate your kind effort and valuable time in writing such a detailed recommendations.

My intention is to get the correct formula in computing opening and closing balances and therefore ignore all other factors such as inventory adjustments, cycle count, spoliage/scrapped etc. Please advise is there a quicker way to get the correct formula.

Thank you again for your kind contribution and consideration.

Thanks & Best Rdgs

Steven

[#6]

Hi stevenlow,

Did you get a solution for your requirement. I have the same problem.

If Oleg, or anybody, Can you give us a short example, it could be nice.

FOR EXAMPLE :

The case where a starting balance exist and an transactions table with sale and Purchase of products.

Product Table

---------------------

ProductId	ProductName
1	prod1
4	prod4
2	prod2
3	prod3

STARTING STOCK

-------------------------------

ProductId	Qty
1	50
2	30
4	10

Transactions

-------------------------

ProductId	TransactionType	TransactionQty	TransactionDate(DD/MM/YYYY)
2	'Sale'	10	21/01/2011
2	'Purchase'	5	21/01/2011
1	'Sale'	2	21/01/2011
3	'Purchase'	3	24/01/2011
1	'Sale'	4	24/01/2011
3	'Sale'	2	27/01/2011

Thanks in advance.

Taxaw

Ce message a été modifié par: taxaw

[andreasasenterp]

Hello everyone!

I want to talk about Inventory Balance. How we can count curent Balance when we know Opening Balance and Periodic operation.

For example, we have curent debt on 01.01.2017:

And you have operation list:

There is and operation which show "in" and "out" payments in list of operation. We need to know Opening and Closing balance on every day.

Lets do it!

tmpOperation:

LOAD Date(Date) as Date,

     ID,

     Amount

FROM

[..\OperationList.xls]

(biff, embedded labels, table is Sheet1$);

Left Join (tmpOperation)

LOAD ID,

     OpeningBalance

FROM

[..\Остатки задолженности.xlsx]

(ooxml, embedded labels, table is Лист1);

Operation:

LOAD Date,

     ID,

     Sum(Amount) as DayBalance,

     Max(OpeningBalance) as OpeningBalance

Resident tmpOperation

Group By ID, Date

Order By ID, Date

;

DROP Table tmpOperation;

InventoryBalance:

LOAD Date,

     ID,

     If(IsNull(Previous(OpeningBalance)) or ID<>Previous(ID),OpeningBalance,Peek(ClosingBalance)) as OpeningBalance,

     DayBalance,

     If(IsNull(Previous(OpeningBalance)) or ID<>Previous(ID),OpeningBalance+DayBalance,Peek(ClosingBalance)+DayBalance) as ClosingBalance

Resident Operation

Order By ID, Date

;

DROP Table Operation;

After that we have table like this: