Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Sum({$<Data = {">=01/01/2000<=$(#Alla)"} >} Giacenza * max( {$<Data = P({1<Data = {">=01/01/2000<=$(#Alla)"} >} Data) >} Costo) )
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
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
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
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
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
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
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
GRAZIE, Ti sei fatto un amico in Italia, Palermo.