Accumulative Totals in a Pivot Table

[JoaquinLazaro]

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

[rubenmarin · Accepted solution]

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.

[ahmar811]

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

[JoaquinLazaro]

Hi Ruben:

Your expression works fine.

I can't check the Full Accumulation option

Thanks

Joaquín

[JoaquinLazaro]

Thanks for your help Ahmar

[rubenmarin]

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

[JoaquinLazaro]

Hello Ruben:

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

[rubenmarin]

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

)

[JoaquinLazaro]

Great.

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