topic Re: Total columns using Pivot tables in QlikView

Total columns using Pivot tables

Mon, 22 Feb 2016 22:06:29 GMT

Good afternoon,

I am having an issue showing column totals for a particular dimension.

The process breaks correctly based upon the dimension as a total line shows up but the totals are not being displayed.

My expression just looks for the values of the dimension that I am interested in and that works.

I added a sum expression for the column that I am looking to total and I am not sure that I am doing that correctly.

Please help.

Thank you,

Mitch

Re: Total columns using Pivot tables

Oleg_Troyansky — Mon, 22 Feb 2016 22:28:04 GMT

Hi Mitch,

it would help if you could post your application, or at least show your Expression formulas.

cheers,

Oleg Troyansky

Re: Total columns using Pivot tables

Mon, 22 Feb 2016 22:33:39 GMT

Table

Process ind Start Value Current Value

C 100 75

C 10000 250

C 2000 120

N 100 50

N 200 0

N 1000 1000

In the pivot table I am trying to break on Process ind and show totals for Start Value and Current Value.

Re: Total columns using Pivot tables

johnw — Mon, 22 Feb 2016 22:50:26 GMT

The general form for sum totals in a pivot table is like this:

sum(aggr(Expression,Dimension1,Dimension2,...))

As Oleg suggests, we can't really give you the exact expressions without knowing your current expressions. But I'm going to guess something like this:

sum(aggr(sum([Start Value]),[Process ind]))
sum(aggr(sum([Current Value]),[Process ind]))

I'm not sure that I've understood the question correctly, though.

Re: Total columns using Pivot tables

Oleg_Troyansky — Mon, 22 Feb 2016 22:58:26 GMT

The actual solution might be even simpler than that... If I'm reading the requirement correctly, this is what you need:

Dimension: Process (with the single dimension, this can be either a Straight Table or a Pivot Table)

Expressions:

sum([Start Value])

sum([End Value])

If you really want to show each value individually, then - yes, add them to the list of Dimensions, too. In this case, request "Partial Sums" for the Process on the Presentation tab.

I think your current problem is caused by the fact that you didn't use sum() in your expressions, and because of that the detailed lines worked just fine, but the totals showed nulls.

cheers,

Oleg Troyansky

Re: Total columns using Pivot tables

Mon, 29 Feb 2016 14:27:51 GMT

Thank you

Sorry for the delay.

I was able to get the totals Thank you

but lost showing each value individually

Re: Total columns using Pivot tables

Oleg_Troyansky — Mon, 29 Feb 2016 18:51:18 GMT

As I mentioned in my reply, - if you want to show individual values too, then keep them as Dimensions and request Partial Sums on the Presentation tab of the properties.

Re: Total columns using Pivot tables

Tue, 01 Mar 2016 21:40:36 GMT

Thanks but even when I check Show Partial Sums I am just getting the totals.

Any other thoughts?

Re: Total columns using Pivot tables

Oleg_Troyansky — Tue, 01 Mar 2016 21:43:23 GMT

Mitchell, you'll have to post your application to get more thoughts. We can't keep guessing what else is wrong with your pivot table.

Re: Total columns using Pivot tables

Tue, 01 Mar 2016 21:54:02 GMT

My only expression is

sum(aggr(sum(

would that be the issue

Re: Total columns using Pivot tables

Oleg_Troyansky — Tue, 01 Mar 2016 23:02:29 GMT

I'd need to see more than that - all chart Dimensions and the full expression - AGGR by what dimensions ? aggregating what fields?

Re: Total columns using Pivot tables

Wed, 02 Mar 2016 22:23:23 GMT

Table has this info:

Process Status Start End

A 100 200

A 100 0

C 500 0

C 250 100

C 400 200

Results looking for are:

ProcessStatus Start End

A 100 200

A 100 0

Total A 300 400

C 500 0

C 250 100

C 100 200

Total C 850 300

Grand Total 1150 700

Dimensions are :

Process Status

Start

End

I have 2 expressions:

1) (aggr(sum(Start),ProcessStatus))

2) (aggr(sum(End),ProcessStatus))

Presentation:

ProcessStatus (Show partial sums)

Start (Show partial sums)

End

This is what I get without the details:

Total A 300 400

Total C 850 300

Grand Total 1150 700

Re: Total columns using Pivot tables

Oleg_Troyansky — Wed, 02 Mar 2016 22:54:08 GMT

OK, now the picture is clear 🙂

Your AGGR() functions aggregate data by Process Status, while the chart is trying to present the data at the detailed level - by Process status, Start, and End. Henric is calling it "grain mismatch", and I call it "unsafe used of AGGR in charts", or breaking the "Third Rule of AGGR()"... Simply put, the aggregated buckets coming out of AGGR() are not granular enough, compared to your chart Dimensions.

You have two choices here: either add Start and End to the dimensions of AGGR(), or avoid using AGGR() altogether, you don't really need it here. Simply use SUM() and it will do the job just fine:

sum(Start)

sum(End)

If you'd like to learn more about AGGR(), I encourage you to check out if Masters Summit for Qlik is a good fit for you. Or, check out my new book QlikView Your Business - I describe there all three rules of AGGR() and a lot more.

cheers,

Oleg Troyansky