8 Replies Latest reply: Oct 20, 2015 5:23 AM by Joaqu�n L�zaro

# Accumulative Totals in a Pivot Table

Hello:

I have a pivot table with two dimensions Product and Month, an expression for the current year sales

SUM( { <Year={\$(=year(today()))}  > } Sales )

I need a second expression to get the aggregated sales by month but I can't type the correct expression.

Would you mind give me a little help, please ?

Joaquín

• ###### Re: Accumulative Totals in a Pivot Table

Hi Joaquin, you can use:

RangeSum(Above(SUM({<Year={\$(=year(today()))}>} Sales), 0, RowNo()))

Using above will disable the option to sort the table double-clicking the header.

Or you can use the same expression you're using in 'Current Year' but checking 'Full Accumulation' below the expression list.

• ###### Re: Accumulative Totals in a Pivot Table

Hi Ruben:

I can't check the Full Accumulation option

Thanks

Joaquín

• ###### Re: Accumulative Totals in a Pivot Table

Yep, it's not available in pivot table, I saw the image and thought in a straight table.

Pivot tables may cause issues with above() if user can expand/collapse the rows, try it before confirm as it's working and consider if 'always expanded' (in presentation tab) is an option you can apply.

And maybe you need to add a TOTAL to rowno:

RangeSum(Above(SUM({<Year={\$(=year(today()))}>} Sales), 0, RowNo(TOTAL)))

• ###### Re: Accumulative Totals in a Pivot Table

Hello Ruben:

The total row is always 0, collapsed or not with or without RowNo(Total)

• ###### Re: Accumulative Totals in a Pivot Table

I don't know if there is a better way but you can use dimensionality() to avoid that:

If(Dimensionality()=0, SUM({<Year={\$(=year(today()))}>} Sales),

RangeSum(Above(SUM({<Year={\$(=year(today()))}>} Sales), 0, RowNo()))

)

• ###### Re: Accumulative Totals in a Pivot Table

Great.

There are two dimensions, it works wtih If(Dimensionality()<=1 ...

• ###### Re: Accumulative Totals in a Pivot Table

SUM( { <Year={\$(=year(today()))},Month={\$(>=max(Month))}  > } Sales )