Problem using Expression in Pivot Chart

[Anonymous]

Hello All, Can someone please help me with an expression in a pivot chart.

I am trying to create a pivot chart which shows per part number per Year/Month the following items:

- Stock(Qty) mutation (column: voorraad mutatie),

- Stock(Qty) level (column: voorraadstand),

- Latest Stock Price (column: VVP).

I have enclosed an example of my dashboard with this message.

The Stock Price is where i am having some problems because this price does not change every month.

This is the tabel of the stockprice changes:

Date	Partcode	NewPrice
15-12-2013	222	25
1-2-2014	222	20
5-3-2014	222	10
10-3-2014	222	5
10-4-2014	222	20
11-5-2014	222	15
15-5-2014	222	1

Stock(Qty) mutations:

Date	Partcode	Mutation
28-11-2013	222	315
15-1-2014	222	5
15-2-2014	222	10
28-3-2014	222	-20
15-5-2014	222	-5
20-6-2014	222	10

I am using  the following expression:

=FirstSortedValue(NEWPRICE_MUT, -STOCKPRICEDATE_Mut)

As you can see in the Pivot where there is a stock(qty) mutattion but there is no NewPricebut it will not find the NewPrice.

I hope someone can tell me where i am going wrong.

[#2]

Hello,

i can see, that you have StockNrYearMonth_Mut as dimension.

Your expression for VVP is evaluated in context of this dimension. So, firstsortedvalue in each cell is calculated only from rows, which are present for particular value of this StockNrYearMonth_Mut.

In my oppinion the easiest way to have price for each date is dividing VOORRAAD_Mut into few tables during reload and then associate price with all months, where this price is valid.

regards

Darek

[Anonymous]

Hello Darek,

Thank you for your reply.

Unfortunately this is not exactly what i am looking for.

I will keep trying to solve this in the expressions.

Regards,

Yuri.