topic Re: Last Inventory Values Date Chart in QlikView

Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 13:17:20 GMT

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

Re: Last Value Date Chart

swuehl — Wed, 23 Sep 2015 13:19:36 GMT

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 )

Re: Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 13:37:07 GMT

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()

Re: Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 13:52:08 GMT

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

Re: Last Inventory Values Date Chart

swuehl — Wed, 23 Sep 2015 14:06:49 GMT

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)

Re: Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 14:23:45 GMT

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

Re: Last Inventory Values Date Chart

swuehl — Wed, 23 Sep 2015 14:39:34 GMT

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) )

Re: Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 14:45:19 GMT

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

And of course thansk again for your great support

Re: Last Inventory Values Date Chart

swuehl — Wed, 23 Sep 2015 14:50:16 GMT

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

Re: Last Inventory Values Date Chart

Anonymous — Wed, 23 Sep 2015 15:01:22 GMT

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

many thanks, got the report as wished

Re: Last Inventory Values Date Chart

Anonymous — Thu, 05 Nov 2015 14:04:24 GMT

just got an error on my chart

And really dont know how to solve this...

If i select a item which currently has stock zero, the whole month will not be displayed, as there is no value. The problem is that we do not store items in the database with stock zero, thats mean no stock, no record. As conclusion the firstsorted value, will not display the month september...

Can you help on this, any idea how to manage?