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?

[tomhovens]

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

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

[Gysbert_Wassenaar]

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.

[MarcoWedel]

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

[tomhovens]

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?

[MarcoWedel]

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

Hope this helps

Regards

Marco

[tomhovens]

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

[jagannalla]

Hi,

May be you are expecting according to attached qlikview file.

Thanks,

Jagan

[tomhovens]

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

[jagannalla]

T:

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;