Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.