Conditional selection by comparing month

Sat, 03 Dec 2011 16:47:09 GMT

Hello,

Please advice. For "Type=A" I want to get sum "Value" if previous month "Direction=S" and current month "Direction=W".

Month,Direction,Value,Type

Sep,N,1,B

Oct,W,2,B

Nov,S,3,A

Dec,W,4,A

Thanks.

Conditional selection by comparing month

swuehl — Sat, 03 Dec 2011 22:52:15 GMT

In a (e.g. straight) table chart object with above layout (dimensions Month, Direction, Type), you could probably use chart inter records like above() function in an expression to achieve this:

= sum( if(Type='A' and Direction='W' and above(Direction)='S', Value))

If you don't want to calculate this within a table chart e.g. in a global context, you could try

= sum(aggr( sum( if(Type='A' and Direction='W' and above(Direction)='S', Value)), Month,Direction,Type) )

But unfortunately, you can't really control the sort order of a aggr() dimension, but you need Month to be sorted ascending (because above() should get the previous Month in correct order), like in your sample above (Sep, Oct, Nov, Dec). I believe this will work if this is your load order for field Month, but if not, that will be a possible issue.

topic Conditional selection by comparing month in QlikView

Conditional selection by comparing month

Conditional selection by comparing month