24 Replies Latest reply: Sep 20, 2012 6:45 PM by Brian Garside

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

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

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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().

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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?

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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

So it accumulates only values that you see in pivot.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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>')

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Oh, thats simple, use:

below(Sum({\$<[Cost Type]={"Actual*"}>}Amount)) <= 0

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Makes sense. For some reason though it went all solid.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

I've tried it before post.

If(below(Sum({\$<[Cost Type]={"Actual*"}>}Amount)) <= 0 AND Sum({\$<[Cost Type]={"Actuals*"},\$(vLastMonth)={"<\$(vNextMonth)"}>}Amount) <= 0,'<S2>','<S1>')

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

This one did the trick....

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

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Ok, that did work. I just had to insert it properly in my If statement. Awesome such a simple and elegant solution.

I really need to start knowing the Chart Inter Record functions!

Thanks So much this one has been bugging me for three days.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

This is the one that works:  If(Below(Sum({\$<[Cost Type]={"Actual*"}>}Amount)) <= 0 ,'<S2>','<S1>')

The one with vMonths was my failed logic I was playing with.  the 'below' function is such a better method maybe the only method!

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Thank you, 'cause I've met that several times before and today I've finally realised how to beat that

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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.

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

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

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

I have a dilemma, I'm trying to solve on my pivot table. My Cumulative is showing data 2 months past where it should? See below....

My Actual Cumulative should stop at September but it keeps going along with my Obligated which should not either.  Any pointers?  Is this expected behavoir? See attached Image

RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo()))

Thanks!

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Yes its expected.

For every column after September your expression can be calculated clearly. And its logically right. There is no actual after September so the result is the same but it exists.

You have to add a condition to make it zero in future if you want, for example:

Count({1<[Cost Type]={"Actual*"}>}Amount) > 0

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Ok, that makes sense now. I had a feeling that would be the case. So I need to nest the Count expression you provided into my RangeSum it sounds like?

Or do I use an If statement and use the count to qualify the Rangesum before its used?

=If( Count({1<[Cost Type]={"Actual*"}>}Amount) > 0 , RangeSum(Before(Sum({1<[Cost Type]={"Actual*"}>}Amount),0, ColumnNo())) , 0 )

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

That Did the trick!  Thanks!

If( Count({1<[Cost Type]={"Actual*"}>}Amount) > 0 , \$(Cum_Actual) , 0 )

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

're welcome

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Hi Whiteline,

I really need some help recreating the values in the blue dotted line into the Pivot table shown in my attachment. I was only able to replicate the accumulated sum for the next month projections after the actual's run out but not on the subsequent ones. 451K on the lien is correct the smaller value in the table is too low marked in red.

I was thinking of using 'After' to calculate the sums going forward?

Here is what I propose to match the dotted portion.

If ( Sum({1<[Cost Type]={"Actual*"}>}Amount) > 0 , \$(Cum_Actual) , \$(Cum_Actual)+Sum({1<[Cost Type]={"Projected*"}>}Amount) )

The +Sum({1<[Cost Type]={"Actual*","Projected*"}>}Amount)  part only works for the Month of Oct. but does not accumulate obviously going forward.

This one wont work because its adds Projected too early. I need Projected to only accumulate once Actual's are   0

RangeSum(Before(Sum({1<[Cost Type]={"Projected*","Actual*"}>}Amount),0, ColumnNo()))

This is what I have in my Chart line Exp for reference. This gets accumulated by the chart accumulation So I naturally I thought the one above would work.

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

I tried this but got weird results...

If ( Sum({1<[Cost Type]={"Actual*"}>}Amount) > 0 , \$(Cum_Actual) , \$(Cum_Actual) + RangeSum(After(Sum({1<[Cost Type]={"Projected*"}>}Amount),0, ColumnNo()))

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Hi, Brian.

Sorry, I think I didn't understand your issue.

But talking about chart you've mentioned ColumnNo() function. I think that's may be a problem.

It seems that 'behind' a chart QV organizes data as a vertically oriented table.

So I think, for manual accumulation you should use RowNo() instead:

=RangeSum(Before(Sum({1<[Cost Type]={"Projected*","Actual*"}>}Amount),0, RowNo()))

• ###### Re: Need an Expression for Full Accumulation/Cumulative in a Pivot table

Yeah my reply is confusing.  Essentaily the numbers I marked in red in the screenshot are so suppose to match up.

The logic is basically this; Actuals are calculated from a left starting point to some place in to the right. The projected will gernarally be present plsu in front of the actuals or 'After' hence why I thought the After clause would do it.

I will try the RowNo() and see what happens.

Thanks!