topic Re: sum of rows in pivot when using column() in QlikView

sum of rows in pivot when using column()

karolina_ — Thu, 22 Jun 2017 07:34:52 GMT

Hi All

I know help says "Pivot table totals are always calculated as expression total."
But maybe someone findout workaround for this so I don't need to change expression:

Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0

to huge sums with aggr?

Each of column is set analysis with variables.

Simple example to play with attached.

Re: sum of rows in pivot when using column()

Anil_Babu_Samineni — Thu, 22 Jun 2017 07:43:12 GMT

Hello Karolina,

What are you expecting and where we need to work.

Re: sum of rows in pivot when using column()

karolina_ — Thu, 22 Jun 2017 07:48:36 GMT

I would like to have total for last column with expression Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0 to be SUM OF ROWS not total of expression
I want to keep Column() function, not rewriting it to huge expression

Re: sum of rows in pivot when using column()

prma7799 — Thu, 22 Jun 2017 07:56:04 GMT

Please share expected output...

Here i am not understanding about >>> " Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0 "

Re: sum of rows in pivot when using column()

karolina_ — Thu, 22 Jun 2017 08:01:49 GMT

Re: sum of rows in pivot when using column()

prma7799 — Thu, 22 Jun 2017 08:07:52 GMT

Convert this pivot chart into straight chart >> Expression > Total Mode >set as Sum

Re: sum of rows in pivot when using column()

karolina_ — Thu, 22 Jun 2017 08:13:01 GMT

Question is about pivot. I know straight table can do this - I even post a screen of it to show needed value.

Re: sum of rows in pivot when using column()

Frank_Hartmann — Thu, 22 Jun 2017 08:41:51 GMT

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

Re: sum of rows in pivot when using column()

karolina_ — Thu, 22 Jun 2017 12:29:26 GMT

In my org app I use sums with set analysis and variables so such expression would be quite big.

I also display part of this expression in different columns for example Column(3)-Column(4) is column(7).

If sum of rows would be somehow possible in pivot I would use simple Column(14)-Column(11)

if not I will need write expression with all sums and aggrs

Re: sum of rows in pivot when using column()

Frank_Hartmann — Thu, 22 Jun 2017 12:47:40 GMT

i guess you will have to write the expressions with sum and aggr.

As Sunny mentioned in this post, column function is not working in this case!

hope this helps

Re: sum of rows in pivot when using column()

karolina_ — Tue, 27 Jun 2017 08:54:13 GMT

At least it accepts variables I use in columns:

sum(aggr(

sum(aggr($(eExpr3),[DIM1],[DIM2]))

-sum(aggr($(eExpr4),[DIM1],[DIM2]))

-if(sum(aggr($(eExpr2),[DIM1],[DIM2])),

(sum(aggr($(eExpr3),[DIM1],[DIM2]))

/sum(aggr($(eExpr1),[DIM1],[DIM2]))

-sum(aggr($(eExpr4),[DIM1],[DIM2]))

/ sum(aggr( $(eExpr2),[DIM1],[DIM2])) )

*sum(aggr($(eExpr1),[DIM1],[DIM2]))

,0)

,[DIM1],[DIM2]))

Thanks Frank