Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
rangesum(Firstsortedvalue(InventoryOld, Date),Firstsortedvalue(InventoryNew,-Date))/2
Do you want to calculate average of inventory for each itemnr monthly or weekly or entire input?
Itemnr, Avg(InventoryNew)
1,43/6
What do you mean with Firstsortedvalue
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 )
can you specify the calculation of the average inventory for your sample data?
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
Hi,
Can you explain little bit more on Column X calculation. At least for two or three rows.
Thanks,
Jagan
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 |
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.