Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
joaquinlr
Valued Contributor 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

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.

View solution in original post

8 Replies

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.

View solution in original post

ahmar811
Contributor III

Re: Accumulative Totals in a Pivot Table

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

joaquinlr
Valued Contributor II

Re: Accumulative Totals in a Pivot Table

Hi Ruben:

Your expression works fine.

I can't check the Full Accumulation option

Thanks

Joaquín

joaquinlr
Valued Contributor II

Re: Accumulative Totals in a Pivot Table

Thanks for your help Ahmar

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

joaquinlr
Valued Contributor II

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

)

joaquinlr
Valued Contributor II

Re: Accumulative Totals in a Pivot Table

Great.

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