Need an Expression for Full Accumulation/Cumulative in a Pivot table

[#1]

I have a Table with a Cost Type Column containing: Obligated, Actual's and Projected across a Month Dimension ranging from Jan-2012 into Feb-2013 a rolling calendar so YTD wont be useful. I just need a 'simple' Cumulative running total based off my set analysis sample below.

I need get have a Cumulative summation from Cost type Obligated & Actual.  See Image below for ref.

The problem is Full accumulation only works in a Chart not a table. I'm new at this so I need a good starting point.

My Measure is : Amount

The Expression I use to display the Obligated Columns is: Sum({$<[Cost Type]={"Obligated*"}>}Amount)

This works fine. But if I want to compare my Actuals against my Obligated Funds which may not be in that Month then its shows I ran out of Funds even though they were allocated in Months past. The end goal is to color code the areas were the Cumulative is < less than the Cumulative Actual's for that month then flag it red.

Cumulative Obligated < Cumulative Actual's Then make Color background Red.

Thank you for any assistance rendered.

Brian

[whiteline · Accepted solution]

I think this expression will give you accumulation of Sum({$<[Cost Type]={"Obligated*"}>}Amount) by month:

=rangesum(Before(Sum({$<[Cost Type]={"Obligated*"}>}Amount),0, ColumnNo()))

You should use "before" and ColumnNo() because your table is right foward.

For vertical accumulation use "above" and RowNo().

[#3]

Everything works great, it replicates the full accumulation. I did run into a User selection issue that 'breaks' the calculation though. Wondering if there is a work around?

Since its based on right forward, if the user selects months after the obligated was allocated it never gets pulled in.

If I change the Set analysis to 1 vs $ that works by forcing it to keep ColumnNo left of the selection in place, but prevents user from de-selecting the ColumnNo. Is there a way I can ignore the month selections in the RangeSum expression or do something in the script which does not allow the Before clause.

Im thinking about ignoring the Month field in the Summation portion ?

=Sum({$<[Cost Type]={'Obligated'},Month=>}Amount)

Or Alter the Dimension Month?

[whiteline]

Thats right the solution with "before" (and so on) works with pivot itself.

So it accumulates only values that you see in pivot.

Describe your case in an example and I'll try to help you.

[#5]

Maybe I need to change the Offset of 0 to a positive N to look further left. ColumnNo can only return what's available to it, so there lies the problem, just not what I need to alter to inlcude all columns even if a user slevts less than that.

Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table columns counting to the left from the original cell. In this form, the function can be used as an argument to any of the special range functions.

[whiteline]

I would say rather "before' function "can only return what's available to it".

[whiteline]

Ok, could you please describe what behaviour you expects from your application ? (the use case).

[#8]

Basically If I select 3 months Jun-Aug it should show just the three months just like the Chart does.

But with the set analysis at 1 vs $ it blocks that ability to select combinations that don't start at the beginning.

In order for the expression to work for the Color coding if The Actual's are greater than the running amount of the obligated.

If the Accumulation is only for those 3 months vs going to the beginning of the data set i.e Jan-12  then you wont get an accurate comaprison.

So its the user vs the Expression... If the user selects the montsh int eh Chart that overides me hiding the Month List box.

[whiteline]

You can try

Only({$} rangesum(Before(Sum({$<[Cost Type]={"Obligated*"}>}Amount),0, ColumnNo())))

The only function reduces the resulting set but I'm not shure if it right in your case.

[#10]

Just tried it. It returns Null values blanks. Reduce I want to keep it open to maximum. The chart accumulation behaves the same way even with the built in accumulation.  I guess the only way around it is to generate the accumulation in the data source or script.... But for now I'll make this work with the {1}  in my set analysis.

The other Issue I ran into is to get the Line to start dash line style in Jul which appears in Aug, Even though its the end of Jul it starts the dash fro Projected .If I coould get it to move back one month.

S2=Dash S1=Line

If(Sum({$<[Cost Type]={"Actual*"}>}Amount) <= 0 ,'<S2>','<S1>')