topic Re: Calculating Inventory Value in QlikView

Calculating Inventory Value

Mon, 22 Oct 2012 21:09:31 GMT

Dear all

I really don't know how to handle that request.

We sould calculate a value in CHF for every Product and Day. (a rolling sum for a Daily Inventory Graph)

I tried to write some code to do that, but it doesn't worked.

Can someone give me a tip?

I tried it like following:

//Loding the Source-Table:

tmpLagerwert:

LOAD

Buchungsdatum as Date,

Artikelnr_ as Product,

Lagerwert as Inventory

FROM

D:\Data\Lagerwert.qvd

(qvd);

//Load it again to sort it by Product, Date

Lagerwert:

NoConcatenate

LOAD

Date,

Product,

If(Previous(Product)=Product,Previous(Inventory)+Inventory,Inventory) as NewInventory

Resident tmpLagerwert Order By Product, Date;

//Here I tried to go the sorted order and test, if it's the same product. If yes, so sum the previous value, if not, so take the base-value

The major problem was either, the total field not exists yet (by loading "as") or I became the error message, that only one field with the same name is allowed in one table.

Any Idea?

Best Regards

Ramon

Re: Calculating Inventory Value

MayilVahanan — Mon, 22 Oct 2012 21:32:21 GMT

Please check the attached file

Hope it helps

Re: Calculating Inventory Value

Tue, 23 Oct 2012 08:04:07 GMT

Hi Mayil

Thanks for your template.

I've not tested yet, but I'm afraid of the performance because we have arround 100 Mio Records (means, a Loop from 1 to 100Mio). I'm not sure if that works.

But I will test it today evening to give a truly feedback.

Greetings

Ramon

Re: Calculating Inventory Value

Tue, 23 Oct 2012 08:31:00 GMT

why dont you use a group by and sum(invetory) for the second table??

Lagerwert:

NoConcatenate

LOAD

Date,

Product,

sum(inventory) as newInvetory

Resident tmpLagerwert group By Product, Date;

regards,

Re: Calculating Inventory Value

Tue, 23 Oct 2012 10:08:06 GMT

Hi kbs_tamm

that's not possible, because I need a SUM from the beginning of the Product till every date:

Date Product Stock Total

01.01 x +100 100 (purchase)

02.01 x -10 90 (sell)

03.01 x -50 40 (sell)

04.01 x +100 140 (purchase)

05.01 x -140 0 (sell)

What I need is the column "Total", for every date and every product in MasterCalendar.

I added a little Demo for get the idea visible.

Greetings

Ramon