topic Accumulative Totals in a Pivot Table in QlikView

Accumulative Totals in a Pivot Table

Joaquin_Lazaro — Tue, 20 Oct 2015 08:45:23 GMT

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

rubenmarin — Tue, 20 Oct 2015 08:52:30 GMT

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

ahmar811 — Tue, 20 Oct 2015 08:54:09 GMT

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

Re: Accumulative Totals in a Pivot Table

Joaquin_Lazaro — Tue, 20 Oct 2015 08:56:02 GMT

Hi Ruben:

Your expression works fine.

I can't check the Full Accumulation option

Thanks

Joaquín

Re: Accumulative Totals in a Pivot Table

Joaquin_Lazaro — Tue, 20 Oct 2015 08:57:04 GMT

Thanks for your help Ahmar

Re: Accumulative Totals in a Pivot Table

rubenmarin — Tue, 20 Oct 2015 09:03:10 GMT

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

Joaquin_Lazaro — Tue, 20 Oct 2015 09:12:35 GMT

Hello Ruben:

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

Re: Accumulative Totals in a Pivot Table

rubenmarin — Tue, 20 Oct 2015 09:15:55 GMT

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

Joaquin_Lazaro — Tue, 20 Oct 2015 09:23:14 GMT

Great.

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