Average inventory

[tomhovens]

Dear qlikviewer,

How to calculate the average inventory in qlikview. please the table

Itemnr	Date	Serialnr	Warehousenr	Mutation	InventoryOld	InventoryNew
1	1-12-2014	0	1	-5	15	10
1	15-11-2014	1	1	2	13	15
1	15-11-2014	0	2	-2	2	0
1	10-11-2014	0	1	10	3	13
1	1-11-2014	1	2	2	0	2
1	1-11-2014	0	1	3	0	3

Extra info: there are a lot of itemnr

Who could help me?

[Gysbert_Wassenaar]

rangesum(Firstsortedvalue(InventoryOld, Date),Firstsortedvalue(InventoryNew,-Date))/2

[anbu1984]

Do you want to calculate average of inventory for each itemnr monthly or weekly or entire input?

Itemnr, Avg(InventoryNew)

1,43/6

[tomhovens]

What do you mean with Firstsortedvalue

[ThornOfCrowns]

From HELP (F1)

firstsortedvalue( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

Examples:
firstsortedvalue ( PurchasedArticle, OrderDate )     

firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )     

firstsortedvalue ( A/B, X*Y/3 )    

firstsortedvalue ( distinct PurchasedArticle, OrderDate )     

firstsortedvalue ( total PurchasedArticle, OrderDate )    

firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate )   

[MarcoWedel]

can you specify the calculation of the average inventory for your sample data?

[tomhovens]

I attached the Qlikviewfile.

The correct answer Qlikview has to calculate is

Itemnr	Date	Serialnr	Warehousenr	Mutation	InventoryOld	InventoryNew	ColumnX	ColumnY
1	1-12-2014	0	1	-5	15	10	16	240
1	15-11-2014	1	1	2	13	15	5	65
1	15-11-2014	0	2	-2	2	0	14	28
1	10-11-2014	0	1	10	3	13	9	27
1	1-11-2014	1	2	2	0	2	0	0
1	1-11-2014	0	1	3	0	3	0	0

Column X: DateRow - DateRowPrevious WHERE warehousenr is equal

Column Y: Column X * InventoryOld

[jagannalla]

Hi,

Can you explain little bit more on Column X calculation. At least for two or three rows.

Thanks,

Jagan

[tomhovens]

Sure,

Column X is like match and index

I look for the next rows when itemnr is equal and warehousenr is equal

Itemnr	Date	Serialnr	Warehousenr	Mutation	InventoryOld	InventoryNew	ColumnX	ColumnY
1	1-12-2014	0	1	-5	15	10	16 (1-12-2014 - 15-11-2014)	240
1	15-11-2014	1	1	2	13	15	5 (15-11-2014 - 10-11-2014)	65
1	15-11-2014	0	2	-2	2	0	14 (15-11-2014 - 1-11-2014)	28
1	10-11-2014	0	1	10	3	13	9 (10-11-2014 - 1-11-2014)	27
1	1-11-2014	1	2	2	0	2	0	0
1	1-11-2014	0	1	3	0	3	0	0

[jagannalla]

That's fine. But how are you getting the values 16,5,14,9. According to you 16(from inventorynew 10-15 is -5). We need the information from which columns you are getting the values.