Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.