Calculating Inventory Value

[#1]

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

[MayilVahanan]

Hi

Please check the attached file

Hope it helps

[#3]

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

[#4]

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

like

Lagerwert:

NoConcatenate

LOAD

Date,

Product,

sum(inventory) as newInvetory

Resident tmpLagerwert group By Product, Date;

regards,

MT

[#5]

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