calculation of inventory costs

[#1]

Good evening.

I have a problem in calculating the cost of inventory.

example:

cost Table

Gruppo Articolo    Data    Costo

1           A    01/01/2011    5

1           A    01/01/2011    10

1           A    01/02/2011    11

1           A    01/03/2011    12

2           B    01/01/2011    40

2           B    02/01/2011    41

2           B    01/02/2011    45

2           B    01/03/2011    45

stock chart

Articolo    Data    Giacenza

A    01/01/2011    1,00

A    01/01/2011    3,00

A    01/02/2011    4,00

A    01/03/2011    3,00

B    01/01/2011    3,00

B    01/01/2011    100,00

B    02/01/2011   

B    01/02/2011    -10,00

B    01/03/2011    -5,00

I would like to find the formula for finding the stock on 02/01/2011 and multiply it by the cost to date of 02/01/2011 for each group

an example

Sum({$<Data = {">=01/01/2000<=$(#Alla)"} >}  Giacenza  *  max( {$<Data = P({1<Data = {">=01/01/2000<=$(#Alla)"} >} Data) >}    Costo) )

but does not work...

ps

sorry for my English

[swuehl]

Hi Massimo,

I do not completely understand, could you maybe give an explicite example on how you want to do the number crunching?

$(Alla) is a date (02/01/2011 in your example)? If it is a date, I don't think you need to convert it to numerical using $(#..), and then you want to compare to a date (01/01/2000) again.

Regards,

Stefan

[#3]

Hi Stefan,

I would, calocolare the average cost at a later date, present in the variable,
with the stock at the date,

considering the date 02/01/2011
as storage = 4.00 x Cost = € 10.00 = 40.00

in the table, SIZES, and not 'the article, but Group

example
Group   Stock  Cost       Stock X Cost

1           4.00     10.00    40.00

[swuehl]

Massimo,

sorry, I still didn't get it.

For example, let's look at the first day only: 01/01/2011

Gruppo Articolo    Data    Costo

1           A    01/01/2011    5

1           A    01/01/2011    10

Articolo    Data    Giacenza

A    01/01/2011    1,00

A    01/01/2011    3,00

So we have two entries for stock and two entries for cost. Do you want to use the average cost per sepcific date (7.5) to apply to the sum of stock (4)? This would result in 30.

or do you want 5*1 + 10 *3, which is 35. I would use latter, if the cost entries and stock change entries have a 1:1 association to get the real inventory cost, but I can't see that 1:1 relation in your data set (one line more in stock chart).

What do you mean with "the average cost at a later date, present in the variable, with the stock at the date". It is the same date in both cases, isn't it?

Sorry that I am a bit slow, it is getting late,

Stefan

[#5]

hi
excuse for my words. are not very good at explaining

from your example, I would get, considering the date of 01/01/2011

(10 x1) + (3 x 10) = 40

(max (costo) * Giacenza) + ( max (costo) *Giacenza)
can you do?

[#6]

Hi Stefan,

I would, calocolare the average cost at a later date, present in the variable,

with the stock at the date,

considering the date 02/01/2011

as storage = 4.00 x Cost = € 10.00 = 40.00

in the table, SIZES, and not 'the article, but Group

example

Group Stock Cost Stock X Cost

1 4.00 10.00 40.00

Da: swuehl

Inviato: lunedì 18 luglio 2011 22:54

A: MassimoFav

Oggetto: - Re: calculation of inventory costs

<http://community.qlik.com/index.jspa> QlikCommunity

Re: calculation of inventory costs

created by swuehl <http://community.qlik.com/people/swuehl> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/133770#133770

[swuehl · Accepted solution]

Hi Massimo,

I think the expression could look like

=sum({1<Data={"$(Alla)"}>} aggr(sum(Giacenza)*max(Costo),Data, Gruppo))

I used aggr to calculate first the sum(Giagenza) * max (Costo) per Data and Gruppo and the sum the results with Data set to $(Alla).

Hope this is close to what you want, but I also hope the idea should be clear.

See also attached application.

I had some difficulties with the date comparison, please ensure that they are really in date format after loading (e.g. in Sheet2, I tried to reformat your Date into YYYY-MM-DD format, which was not successful, this indicates that Data is not in date format, but text).

Regards,

Stefan

[#8]

GRAZIE, Ti sei fatto un amico in Italia, Palermo.