topic Re: Pivot Table Totals not showing unless fully expanded in QlikView

Pivot Table Totals not showing unless fully expanded

Wed, 18 Jul 2012 09:20:22 GMT

Dear all,

I have a question regarding the Pivot table totals. I know that there are already several discussions about this topic but so far I have not found a proper solution.

I have a Pivot table with 4 levels, on each level I show the totals. This works for my first 3 expressions but not always for the other ones.

The set-up of my Pivot table:

4 dimensions=

DIM1

DIM2

DIM3

DIM4

All my expressions have the following structure:

Expr=if(DIM3='ABCD',[Test cases assessed],count([# of tc performed]))

So basically I want to display a different value if DIM3 equals ABCD in the other case the count needs to be performed. If I fully expand my pivot table the fields are correctly calculated and QV is always able to calculate the count (if applicable) the problem is that I don't get the total values in case DIM3=ABCD.

Does anyone have an idea to what this might be related? Could it be a formatting issue of my data? Or is there something wrong in my formula?

Thanks in advance.

Kind regards,

Jens

Re: Pivot Table Totals not showing unless fully expanded

Jason_Michaelides — Wed, 18 Jul 2012 09:42:36 GMT

Hi Jens,

Count() is an aggregation so will display a total. Where DIM3='ABCD' you are just displaying a different field and there is no aggregation to perform, so the totals will not work. Try this as your expression instead:

=if(DIM3='ABCD',Sum([Test cases assessed]),count([# of tc performed]))

Hope this helps,

Jason

Re: Pivot Table Totals not showing unless fully expanded

jonathandienst — Wed, 18 Jul 2012 09:47:48 GMT

Jens

I think the problem is that DIM3 does not have a defined value at the higher total levels, so your expression returns null. The solution is an aggr expression, like this:

Sum(Aggr(If(DIM3='ABCD',[Test cases assessed],count([# of tc performed])), DIM1, DIM2, DIM3, DIM4))

Hope that helps

Jonathan

Re: Pivot Table Totals not showing unless fully expanded

Jason_Michaelides — Wed, 18 Jul 2012 10:29:02 GMT

D'oh! My brain's not working this morning... Nice one Jonathan

Re: Pivot Table Totals not showing unless fully expanded

Wed, 18 Jul 2012 14:01:21 GMT

Thanks Jonathan for your solution. It is working fine!

Maybe a small additional question: what if I want the total of precentages? If I use Sum(aggr()). It gives off course a wrong number.

Does anyone know what might be the correct way to calculate this?

Re: Pivot Table Totals not showing unless fully expanded

jonathandienst — Thu, 19 Jul 2012 09:37:27 GMT

Jens

You can calculate a ratio or percentage by including the ratio inside the sum (if sum of rows makes sense), or do the ratio of two Aggr expressions (which is the calculation applied across the data set).

Does that help to answer your question?

Jonathan

Re: Pivot Table Totals not showing unless fully expanded

Thu, 19 Jul 2012 09:54:02 GMT

Hi Jonathan,

This indeed solved the issue.

Thanks!