Accumulation in Pivot Table

[#1]

Hi everyone,

I’m trying to accumulate some values inside a Pivot Table (so I can use horizontal dimensions). The challenge is that I need to accumulate month by month only certain values whilst using non-accumulated values for the second part of the expression.

I’m attaching a sample file in which the example will become clearer.

Thanks in advanced!

[Anonymous]

How to sum across rows in a pivot table (running total)

Attached is how to do it with your example .

[#3]

Hello Michael, ive already try using the function "Before" but it wont work because i need to sum the accumulation of values "Type A" and then sum the corresponding "Value B"

[Nicole-Smith]

From what I'm seeing, it looks like you can't do it in one expression.  In my attached file, when I calculate the accumulation of A and just the sum of B in two different expressions, then add those two together, it works fine.  The problem comes in when I try to put the two expressions into one.  Maybe someone else can chime in if they have any ideas because I'm stumped .

[#5]

Thanks Nicole but that is exactly the problem i need them to be in just one expression

[Anonymous]

Try the attached.  I mapped the months to periods.  That way you can multiply the month by Value A and add Value B.

So Jan = 1 so   1*Value A +Value B

     Feb = 2 so  2*Value A +Value B

and so on.

[b_garside]

Try a formula like this;

This works Left to right. Pivot table

RangeSum(Before(Sum(YourField),0, ColumnNo()))

Top to bottom

RangeSum(Above(Sum(YourField),0, RowNo()))

[Nicole-Smith]

Got it!

=RangeSum(Before(sum({$<Type={'Value A'}>} Value),1,12), sum({$<Type={'Value A'}>} Value))+sum({$<Type={'Value B'}>} Value)

Also see the attached.

EDIT:  I forgot to mention that the 1,12 in the before() is accumulating 12 steps back (1 year), as I wasn't sure on your exact requirement.  If you want to accumulate over everything, replace the 12 with ColumnNo(TOTAL).

[Clever_Anjos]

I´m not sure if I understand you requirement, but try this:

rangesum(
  sum({$<Type={'Value B'}>} Value),
  Before(
  sum({$<Type={'Value A'}>} Value),
  0,
  ColumnNo(TOTAL)
  )
)

[Anonymous]

Well your solution is more elegant than mine as usual .