Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

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.

Image.JPG

Would you mind give me a little help, please ?

Joaquín

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

8 Replies
rubenmarin

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
Creator III
Creator III

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

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II
Author

Hi Ruben:

Your expression works fine.

I can't check the Full Accumulation option

Thanks

Joaquín

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II
Author

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
Partner - Specialist II
Partner - Specialist II
Author

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
Partner - Specialist II
Partner - Specialist II
Author

Great.

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