topic Re: Pivot table with aggregated columns in QlikView

Pivot table with aggregated columns

Anonymous — Mon, 11 Jun 2018 12:16:05 GMT

I want to create a Pivot Table where I will show Items sold and profit per year (row). In addition, I want to show a third metric column with the aggregated profit from first row until each row. For example, forth row (2014) will be the sum profit of 2011 + 2012 + 2013 + 2014.

I'm trying to get it with Set Analisis, AGGR and TOTAL functions, without success. Please, anyone knows how can I get it?

Thanks in advance

Cheers

Re: Pivot table with aggregated columns

sunny_talwar — Mon, 11 Jun 2018 12:18:19 GMT

May be try this

RangeSum(Above(Sum(Profit), 0, RowNo()))

Re: Pivot table with aggregated columns

marcus_sommer — Mon, 11 Jun 2018 12:19:52 GMT

You need interrecord-functions for this tasks, for example:

rangesum(above(sum(Profit), 0, rowno(total)))

- Marcus

Re: Pivot table with aggregated columns

Anonymous — Mon, 11 Jun 2018 12:21:43 GMT

Thanks!!, it works but the partial sum (default row with total) is not calculated, is it normal?

Re: Pivot table with aggregated columns

Anonymous — Mon, 11 Jun 2018 12:21:57 GMT

Thanks Marcus!

Re: Pivot table with aggregated columns

sunny_talwar — Mon, 11 Jun 2018 12:23:21 GMT

You might be able to use Dimensionality() function to calculate a partial sum... but I am not sure what the partial sum needs to look like for accumulated value?

How to use - Dimensionality()

Re: Pivot table with aggregated columns

Anonymous — Tue, 12 Jun 2018 07:37:20 GMT

Thanks, I will check it