18 Replies Latest reply: Dec 18, 2014 6:46 AM by Jagan Nalla

# Average inventory

Dear qlikviewer,

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

ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNew
11-12-201401-51510
115-11-20141121315
115-11-201402-220
110-11-20140110313
11-11-201412202
11-11-201401303

Extra info: there are a lot of itemnr

Who could help me?

• ###### Re: Average inventory

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

• ###### Re: Average inventory

What do you mean with Firstsortedvalue

• ###### Re: Average inventory

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 )

• ###### Re: Average inventory

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

Itemnr, Avg(InventoryNew)

1,43/6

• ###### Re: Average inventory

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

• ###### Re: Average inventory

I attached the Qlikviewfile.

The correct answer Qlikview has to calculate is

ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNewColumnXColumnY
11-12-201401-5151016240
115-11-20141121315565
115-11-201402-2201428
110-11-20140110313927
11-11-20141220200
11-11-20140130300

Column X: DateRow - DateRowPrevious WHERE warehousenr is equal

Column Y: Column X * InventoryOld

• ###### Re: Average inventory

Hi,

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

Thanks,

Jagan

• ###### Re: Average inventory

Sure,

Column X is like match and index

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

ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNewColumnXColumnY
11-12-201401-5151016 (1-12-2014 - 15-11-2014)240
115-11-201411213155 (15-11-2014 - 10-11-2014)65
115-11-201402-22014 (15-11-2014 - 1-11-2014)28
110-11-201401103139 (10-11-2014 - 1-11-2014)27
11-11-20141220200
11-11-20140130300
• ###### Re: Average inventory

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.

• ###### Re: Average inventory

16 = 1 december - 15 november
5 = 15 november - 10 november

14= 15 november - 1 november
9= 10 november - 1 november

• ###### Re: Average inventory
```Temp:
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
];

Result:
LOAD *, ColumnX * InventoryOld as ColumnY;
LOAD *, if(Itemnr&'|'&Warehousenr=previous(Itemnr&'|'&Warehousenr) ,rangesum(Date,-previous(Date)),0) as ColumnX
Resident Temp
Order by Itemnr, Warehousenr, Date asc;

drop table Temp;
```

See attached qvw.

• ###### Re: Average inventory

Hi,

one solution could be:

```tabInvent:
FROM [http://community.qlik.com/thread/146100] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabInvent)
ColumnX * InventoryOld as ColumnY;
Warehousenr,
Date,
InventoryOld,
If(Itemnr=Previous(Itemnr) and Warehousenr=Previous(Warehousenr),Date-Previous(Date),0) as ColumnX
Resident tabInvent
Order By Itemnr, Warehousenr, Date;
```

hope this helps

regards

Marco

• ###### Re: Average inventory

This is the original script

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='\$#,##0.00;(\$#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT32 TO VHP ;

SQL SELECT Aantal-mut as Mutation,
Aantal-nieuw as InventoryNew,
Aantal-oud as InventoryOld,
Artikelnr as Itemnr,
Datum as Date,
Magazijnnr as Warehousenr,
Volgnummer as Serialnr
FROM VOV_Voorraad_verslag WHERE subsystem = 0 ;

How to put your script in this script?

• ###### Re: Average inventory

Just replace my first load (tabInvent) with your SQL select and name it tabInvent: also.

Hope this helps

Regards

Marco

• ###### Re: Average inventory

Almost.... there is a problem when:

- Date is equal

- Warehousenr is equal

- Multiple serialnr's

 Date Itemnr Serialnr Warehousenr InventoryOld Mutation InventoryNew Days DaysInventory Days has to be 17-Apr-14 405015 1 1 7 -3 4 27 189 0 17-Apr-14 405015 0 1 4 3 7 0 0 27 21-Mar-14 405015 1 1 5 -1 4 1 5 0 21-Mar-14 405015 0 1 14 -9 5 0 0 1 20-Mar-14 405015 1 3 0 6 6 0 0 0 20-Mar-14 405015 0 1 20 -6 14 344 6880 344
• ###### Re: Average inventory

Hi,

May be you are expecting according to attached qlikview file.

Thanks,

Jagan

• ###### Re: Average inventory

Could post the script? We start in 2015 with Qlikview, till then I need to work with the trialversion

• ###### Re: Average inventory

T:

LOAD *,Itemnr&' '&Warehousenr&' '&Date as Key;

Date#(Date,'DD-MMM-YYYY') as Date,

Serialnr,

Warehousenr,

Mutation,

InventoryOld,

InventoryNew

FROM

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

T1:

,AutoNumberHash128(Itemnr&' '&Warehousenr) as _CombinedKey,

Key as TKey

Resident T Order By Date asc;

Join

,If(_CombinedKey=Previous(_CombinedKey),CurrDate-Previous(CurrDate),0) as Days

Resident T1 Order By _CombinedKey asc;

Join(T)