Find previous non-consistent value?

[Anonymous]

Hello,

We have a filter for 'lothistoryid'. When i select a value (Red) is it possible to show previous value (Blue)

As you can see these do not run in a sequential order. 

[petter]

Yes it is. You can use a set expression with the $1 set identifier which is used for the previous selection:

your existing expression:

Sum( [Stock Value])

summing over the previous expression:

Sum( {$1} [Stock Value])

[Anonymous]

This does not work. Say if i select 521 (Red) i want the previous expression to select 252 (Blue) but it doesn't.

[petter]

If you first select the Blue and confirm it and then afterwards select the Red and then confirm it ... it should work. It has always worked for me.

[petter]

Maybe I misunderstood what you meant by "when I select a value"...

Do you mean that the previous value should always be the next lower lothistoryid?

[Anonymous]

Yes, Exactly. Is that possible?

[petter]

Yes it is possible. This is a quick hack but it works independent of any load script changes and as long as there is not a huge amount of lothistoryids....

Create a variable named vLHid with the expression:

  =Concat({1} DISTINCT lothistoryid,',',lothistoryid)

Create a variable named vPrev with the expression:

   =Alt(Pick(Match(Max(lothistoryid),$(vLHid))-1,$(vLHid)),0)

The expression for the chart should then be like this:

  Sum( {1<lothistoryid={$(vPrev)}>} [Stock Value])

Lastly in on the "previous" chart put in Add-Ons / Data handling / Calculating Condition the expression:

     vPrev>0

and Displayed message:

     There is no lothistoryid before the selected one