Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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) )
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 )
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()
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
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)
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
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) )
almost perfect, just need the bars stacked ... Is there a solution?
And of course thansk again for your great support
With two dimensions and one expression, have you tried the stacked bars option in style tab of chart properties?
Perfect it works, had just a wrong entry in the dimension
many thanks, got the report as wished