Last Inventory Values Date Chart

[Anonymous]

Hey Clickers

Im currently struggling a bit to show the correct values in a bar chart. The Chart is actaully showing the stock history, which were runing  correct, as long we processed the stock update just once a month. Since September we have started as a trial to update the figures once a week and store this value on the database. Befor it was quite easy as i could just use the sum(), now i tried to get the last figures of a period by using maxstring() function, but this function is just showing the overall last value and is therefore not showing the history anylonger. Perhaps i did it wrong.

Sample how the stock values gets accummulated for september

Stockupdate Files (just a total)

How should i modify the expression

sum(#InventoryOnHand_EUR)

so that i get just the last value of a specific Period (Jul-2015, Aug-2015)? The Barchart is just using Month-Year as Dimention (later also Week-Year) and the sum of the inventory value as expression

Can somebody help me out on this

Many thanks

[swuehl]

Try FirstSortedValue(), something like

=FirstSortedValue( [Value X], -Date )

edit: If you need to aggregate the Value X field values, maybe like

=FirstSortedValue( aggr( Sum([Value X]), Date), -Date )

[Anonymous]

Did enter the expression, almost done Now just september is gone with the actual values from 22.09.15

=FirstSortedValue( aggr( Sum([#InventoryOnHand_EUR]), Date), -Date )

sum()

[Anonymous]

for month-year just the september isnt displayed, it could work, not sure because i have just one inventory record for Jul and Aug-215

did check also if i use week-year as dimension, there is no result

I have added the qvd

[swuehl]

We need to look at the records where there is an Inventory entry (your calendar dates for september show max dates where there are no records, this will lead to no Inventory value returned):

=FirstSortedValue({<%Warehouse= {"*"}>} aggr(Sum([#InventoryOnHand_EUR]), Date), -Date)

[Anonymous]

Cool it works

But i got en additional problem, before i was using 2 Dimension, first month-week and a second to split the bar into material groups (Raw, Semi, Finish Goods). Now this split seems not to work anylonger, tried also with differnd dimension, is always showing the same one.

Many thanks for your support

[swuehl · Accepted solution]

You might need to add the second dimension also the the dimensions within FirstSortedValue (assuming %Company as second dimension here):

=FirstSortedValue( {<%Warehouse= {"*"}>} aggr(Sum([#InventoryOnHand_EUR]), %Company, Date), aggr(-Date,%Company, Date) )

[Anonymous]

almost perfect, just need the bars stacked ... Is there a solution?

And of course thansk again for your great support

[swuehl]

With two dimensions and one expression, have you tried the stacked bars option in style tab of chart properties?

[Anonymous]

Perfect it works, had just a wrong entry in the dimension

many thanks, got the report as wished