topic Re: Closing Stock Amount Calculation in QlikView

Closing Stock Amount Calculation

koushik_btech20 — Tue, 09 Jun 2015 05:34:41 GMT

Hi all,

I have an issue in calculating closing stock amount.My requirement has been given in the attached application.Please follow the application and if you have any solution please share.I think it will calculated from the script end.

Thank You in advance.

Koushik

Re: Closing Stock Amount Calculation

pokassov — Tue, 09 Jun 2015 05:48:06 GMT

Hello!

Try this:

FirstSortedValue({$<[Reciept QTY]={'>0'}>} [Reciept QTY],-DATE)

/FirstSortedValue({$<[Reciept QTY]={'>0'}>} [Reciept Amount],-DATE)

*sum({<DATE={'$(=Max(DATE))'}>}[Closing Stock Qty])

And one note:

1195/191913.97*33=0.205.... instead your 5288.77

Best regards, Sergey

Re: Closing Stock Amount Calculation

tresB — Tue, 09 Jun 2015 06:00:48 GMT

Check attached qvw. Your calculated figure is wrong, it should be something like: 0.2054...

Re: Closing Stock Amount Calculation

koushik_btech20 — Thu, 11 Jun 2015 06:18:13 GMT

It is not working properly as per the requirement. pokassov‌'s solution is working properly as per my previous requirement but I am posting the updated logic with attached application.

For any RecID,ITEM,Warehouse,Color, DimID combination if last row have QTY_StockIn value with >0 then that row will be considered otherwise any other previous row on behalf of the last row where the QTY_StockIn value is >0 then that QTY_StockIn value and its corresponding AMT_StockIn value should be considered to calculate the m_AMT_StockCL. And also apply the logic using RecordId because it is unique.Do not depend on the date as it cant be in a sorted manner but recordid corresponding to that date will always be unique.

Ex:- On belhalf of the attached Application,

as per the example

Inventory Closing Qty:1200

Here on the last record,

QTY_StockIn = -1350

but we have to find in which last entry QTY_StockIn is >0,

So on the previous record, QTY_StockIn is 1350 then that record will be considered, but for that case we find >0 value for QTY_StockIN on last previous record and it is not static for all cases we can find anywhere the last >0 value QTY_StockIn, If Last record value have QTY_StockIn >0 then that record should be considered for taking IN QTY and Amount.

So The m_AMT_StockCL=

[

107211.53

1350

]

*1200(Inventory Closing Qty) =95299.14

I think implementing that logic inside script will be a great thing. Please check and if find any solution then share here.

Re: Closing Stock Amount Calculation

koushik_btech20 — Thu, 11 Jun 2015 12:40:48 GMT

Please help anyone I am waiting. Its very urgent.