topic Re: Average inventory in QlikView

Average inventory

tomhovens — Wed, 17 Dec 2014 12:59:34 GMT

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?

Re: Average inventory

Gysbert_Wassenaar — Wed, 17 Dec 2014 13:09:47 GMT

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

Re: Average inventory

anbu1984 — Wed, 17 Dec 2014 13:11:19 GMT

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

tomhovens — Wed, 17 Dec 2014 14:15:37 GMT

What do you mean with Firstsortedvalue

Re: Average inventory

ThornOfCrowns — Wed, 17 Dec 2014 14:20:14 GMT

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

MarcoWedel — Wed, 17 Dec 2014 14:52:39 GMT

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

Re: Average inventory

tomhovens — Wed, 17 Dec 2014 14:52:43 GMT

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

Re: Average inventory

jagannalla — Wed, 17 Dec 2014 15:10:34 GMT

Hi,

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

Thanks,

Jagan

Re: Average inventory

tomhovens — Wed, 17 Dec 2014 15:16:47 GMT

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

Re: Average inventory

jagannalla — Wed, 17 Dec 2014 15:37:30 GMT

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

tomhovens — Wed, 17 Dec 2014 17:52:20 GMT

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

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

Re: Average inventory

Gysbert_Wassenaar — Wed, 17 Dec 2014 19:09:09 GMT

Temp:
LOAD * INLINE [
    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

MarcoWedel — Wed, 17 Dec 2014 21:41:28 GMT

Hi,

one solution could be:

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

Left Join (tabInvent)
LOAD *,
    ColumnX * InventoryOld as ColumnY;
LOAD Itemnr,
    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

tomhovens — Thu, 18 Dec 2014 06:50:14 GMT

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

MarcoWedel — Thu, 18 Dec 2014 07:12:04 GMT

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

Hope this helps

Regards

Marco

Re: Average inventory

tomhovens — Thu, 18 Dec 2014 08:09:12 GMT

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

jagannalla — Thu, 18 Dec 2014 10:47:40 GMT

Hi,

May be you are expecting according to attached qlikview file.

Thanks,

Jagan

Re: Average inventory

tomhovens — Thu, 18 Dec 2014 10:58:42 GMT

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

Re: Average inventory

jagannalla — Thu, 18 Dec 2014 11:46:29 GMT

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

LOAD Itemnr,

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

Serialnr,

Warehousenr,

Mutation,

InventoryOld,

InventoryNew

FROM

C:\Users\Raja\Downloads\Avg.xls

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

T1:

LOAD Distinct Date as CurrDate

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

Key as TKey

Resident T Order By Date asc;

Join

LOAD CurrDate

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

Resident T1 Order By _CombinedKey asc;

Join(T)

LOAD

TKey as Key,

Days

Resident T1;

DROP Table T1;