1 Reply Latest reply: Dec 3, 2011 5:52 PM by Stefan Wühl RSS

    Conditional selection by comparing month

      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
          Stefan Wühl

          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.