topic Re: Pivot Table: total row aggregated over dimension in QlikView

Pivot Table: total row aggregated over dimension

Fri, 11 May 2012 08:18:03 GMT

Hello,

I have attached an application to explain my problem.

I want a pivot table as below with a final row containing the two totals values of expressions (1,2,3) aggregated by the "Measure" dimension.

The measure dimension is a inline table without connexion with data. i just use it for have a "label dimension" in order to use two formula by expression dependant of the "Measure" label.

The table I want.

Category	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
C1	MeasureB	0	100001,4	100001,4
C2	MeasureA	1	0	1
C2	MeasureB	0	0	0
C3	MeasureA	1	0	1
C3	MeasureB	0	555	555
C4	MeasureA	0	0	0
C4	MeasureB	0	0	0
Total	MeasureA	4	0	4
Total	MeasureB	0	100556,4	100556,4

The one I obtain using "partial sum":

Category	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
	MeasureB	0	100001,4	100001,4
	Total	0	0	2
C2	MeasureA	1	0	1
	MeasureB	0	0	0
	Total	0	0	1
C3	MeasureA	1	0	1
	MeasureB	0	555	555
	Total	0	0	1
C4	MeasureA	0	0	0
	MeasureB	0	0	0
	Total	0	0	0

The total values must be processed when we do fields selection, so I can't process the totals values in the load script.

The only method I have found to have this is to use two pivot tables: one for the total, one for other rows.

But I want only one pivot table.

Could you help me?

Re: Pivot Table: total row aggregated over dimension

IAMDV — Fri, 11 May 2012 08:59:40 GMT

Hi,

I wanted to make sure if I have understood your question. Do you want to show the Sub-totals for each Measure (Field)? I mean in your example do you wanted to see Total for each Category where the calculation is MeasureA + MeasureB, is this right?

If this is incorrect assumption then please can you quickly mockup a table showing what is the output you are expecting.

Thanks,

Re: Pivot Table: total row aggregated over dimension

Fri, 11 May 2012 12:43:38 GMT

Hi,

I want on "Total - MeasureA" the total of MeasureA rows, same thing for B.

For example:

Expr1 we have MeasureA C1->2, C2->1, C3->1 and C4->0. So on "Total - MeasureA" I want the sum : 2+1+1+0=4

and on "Total - MeasureB" : 0+0+0+0=0

I want Total MeasureA over dimension Category, and the same for MeasureB as in my first table.

Thanks.

Re: Pivot Table: total row aggregated over dimension

IAMDV — Fri, 11 May 2012 13:33:15 GMT

Hi Again,

Now I understand the requirement. Your "Total Temp Solution" table was hidden in your original example and that's the reason why I couldn't follow you completely. Anyway, I had to tweak two things to get this working:

1. I have added "Total" value in the Category field in the Load Script. After reloading the document, it shows up "Total" at the end under Category Dimension and you can also remove it by using calculated expression if it's not required in other objects.

2. I don't like to use IF statements because it's massive overhead when your data fields have high cardinality. However, this still works as a solution and I'll leave it your judgement.

_____________________________________________________________

Here is the expression you should be using :

IF(Measure='MeasureA' and Category <> 'Total',

Count(Distinct Id),

IF(Measure='MeasureA' and Category = 'Total',

sum(TOTAL AGGR(COUNT(Distinct Id), Category)),

)

_____________________________________________________________

Explanation of above expression :

Step 1 : We are checking both the condition i.e (If the Measure = 'MeasureA' and Category <> 'Total' then we will use your original expression which is :

Count(Distinct Id)

Step 2 : Once the QV engine evaluates "False" for the above expression then we are checking the condition i.e. (If the Measure = 'MeasureA' and Category = 'Total' then we will use this expression :

Sum(TOTAL AGGR(COUNT(Distinct Id), Category))

This expression aggregates and calculates the total. Which is 4 in your example.

Step 3: If both the conditions are not "True" then QV assigns 0. So all the values with Measure = 'MeasureB' having 0.

I'm also attaching the QV document for your reference.

I hope this helps!

Good luck!

Cheers,

www.QlikShare.com

Re: Pivot Table: total row aggregated over dimension

Fri, 11 May 2012 15:58:07 GMT

Thank you!

It seems a quite good solution.

I have just added this to my load script in order to not disturb other objects using "Category" field without totals:

SET NULLINTERPRET=NULL;

CategoryMap:
LOAD Category, Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
Category,CategoryMap
NULL,Total
];
SET NULLINTERPRET=;

I use only for my table with total line the field "CategoryMap" instead of field "Category", and it's perfect.

Re: Pivot Table: total row aggregated over dimension

IAMDV — Fri, 11 May 2012 16:06:32 GMT

I'm happy to know that you have got it working. It was interesting question, which made me think. Thanks for that

Not sure if you have massive application. But if you have massive application then you could do Partial Reload for these kind of scenarios. This is ideal situation to use Partial Reload. Incase, if you wanted more understanding on Partial Reload then please check the below post on my blog :

http://qlikshare.com/370

Have a great weekend.

Cheers - DV

Pivot Table: total row aggregated over dimension

Mon, 14 May 2012 08:08:37 GMT

Hi DV,

I was looking for your solution because it is very interesting.

May I ask you a thing!?

Your solution with MeasureA works perfectly.

What about MeasureB? I used your solution with MeasureB but i don't obtain the result 100556,4 .

Am I making a mistake?

I used:

IF(Measure='MeasureB' and Category <> 'Total',

Count(Distinct Id),

IF(Measure='MeasureB' and Category = 'Total',

sum(TOTAL AGGR(COUNT(Distinct Id), Category)),

)

I'm sorry but your solution is very nice and I would like to use it.

Thanks a lot

Giampiero

Re: Pivot Table: total row aggregated over dimension

IAMDV — Mon, 14 May 2012 08:50:07 GMT

Hi Giampiero,

I'm glad that you find this thread interesting and don't have to be sorry. This whole purpose of having this community is to help each other. I learn a lot from all of you guys. So it's okay mate.

I'm assuming that you are talking about Expr2. And the original expression for Exp2 is different from Expr1. So please try the below expression and it should work :

IF(Measure='MeasureB' and Category <> 'Total',

SUM( aggr(NODISTINCT Min(ValueB),Category,Id)),

IF(Measure='MeasureB' and Category = 'Total',

SUM(total aggr(NODISTINCT Min(ValueB),Category,Id)),

)

I'm also attaching the example for your reference.

Cheers,

www.QlikShare.com

Pivot Table: total row aggregated over dimension

Mon, 14 May 2012 09:03:00 GMT

Hi DV,

thank you so much.

I think the same thing about this community.

I often try to help and to be helped 🙂

You're right, I didn't use the expression 2. Now it works perfectly.

I like a lot your solution. It will be a good start point to solve similar problem in the future.

Again, THANK YOU SO MUCH.

Giampiero

Re: Pivot Table: total row aggregated over dimension

IAMDV — Mon, 14 May 2012 09:12:36 GMT

Hi Giampiero,

Great! You are welcome mate. I'll make a video post on this topic and post it on my blog.

Cheers,

www.QlikShare.com

Re: Pivot Table: total row aggregated over dimension

Mon, 14 May 2012 14:14:42 GMT

I had just a little problem because on Category field value selections I lost my Total row.

To solve it I just replace my previous inline table like this:

SET NULLINTERPRET=NULL;

CategoryMap:
LOAD Category, Category as CategoryMap
resident Category;
Concatenate(CategoryMap)
Load * INLINE [
Category,CategoryMap
NULL,Total
];
SET NULLINTERPRET=;

By just a list independent of data selections

CategoryMap :

LOAD Category as CategoryMap

resident Category;

Concatenate(CategoryMap)

Load * INLINE [

CategoryMap

Total

];

Then use this field CategoryMap in pivot table first dimension expression as below:

=If(CategoryMap=Category or CategoryMap='Total',CategoryMap)

So the total row don't be hidden if I select C1 and C2 Category value, for example.

And as the "Total" value isn't linked to Category field values expressions are automatically set to total value without the

If(CategoryMap='Total') condition. I have just need to set this condition on total value for expression using AGGR function, not for simple Sum and Count expression.

My row Total isn't hidden now when I do selection on field "Category".

I just see something strange now: why C1 Exr2 measureB is 100001.4 and not 100000.95 ?

min valueB for Id 1 and Category C1 is 0.45 and for Id 9 Category C1 it's 100000.5

Normally the sum must be 100000.95, not 100001.4

I have another question, but without link with this subject:

Is it possible to do something like this on this pivot table?:

		MyFirstSuperHeader		MySecondSuperHedaer
CategoryMap	Measure	Expr1	Expr2	Expr3
C1	MeasureA	2	0	2
	MeasureB	0	100001,4	100001,4
	MeasureA	1	0	1
C2	MeasureB	0	0	0
C3	MeasureA	1	0	1
C3	MeasureB	0	555	555
C4	MeasureA	0	0	0
C4	MeasureB	0	0	0
Total	MeasureA	3	0	0
Total	MeasureB	0	100556,4	0

Currently I made this with two horrible text object which don't autosize when my pivot table size change.

I have already tried to do some sort of inline table with headers/super headers, but I can't hide/delete column when expressions is null. I had something like this

Parent Header 1			Parent Header 2
Expr1	Expr2	Expr3	Expr2		Expr3

even with "Supress When Value is Null" checked on my "inline" dimension ParentHeader and Expressions defined with a condition like

IF(ParentHeader='ParentHeader1',***) I have all expressions on each of my horizontal dimension values.

Re: Pivot Table: total row aggregated over dimension

Wed, 16 May 2012 10:01:24 GMT

Hello,

Could someone help me on this 2 subjects above:

Incorrect expression values on my table
Super headers for specific expressions.

The first point is the most important. I tried some formula but it doesn't fix it. Perhaps I use bad syntax?

My sum expression don't display the good result.

Example attached.

Re: Pivot Table: total row aggregated over dimension

Wed, 16 May 2012 15:31:40 GMT

I have finaly found a formula which give me the good result.

I don't know if there are better formulas to do this.

But now I want my "total" Category field value remain when I do selection on the field Category (directly on the table or by a selection list)

In the application attached here, I have two table (aggregate 1 & 2).

If I select Category values by the list, I have always the good total value on my table aggregate1, but not on the second, aggregate2.

If I select directly on the table aggregate1 dimension, I lose the total rows.

On the second table I lose always the total values when I do selections.

Could you help me? I want the total row remain on my pivot when I select Category field values.

Thanks.