Pivot Table: total row aggregated over dimension

[#1]

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
	MeasureB	0	100001,4	100001,4
C2	MeasureA	1	0	1
	MeasureB	0	0	0
C3	MeasureA	1	0	1
	MeasureB	0	555	555
C4	MeasureA	0	0	0
	MeasureB	0	0	0
Total	MeasureA	4	0	4
	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?

[IAMDV]

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,

DV

[#3]

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.

[IAMDV · Accepted solution]

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)),

0

   )  

)

_____________________________________________________________

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,

DV

www.QlikShare.com

[#5]

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.

[IAMDV]

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

[#7]

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)),

0

   )  

)

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

Thanks a lot

Giampiero

[IAMDV]

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)),

0

   )  

)

I'm also attaching the example for your reference.

Cheers,

DV

www.QlikShare.com

[#9]

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

[IAMDV]

Hi Giampiero,

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

Cheers,

DV

www.QlikShare.com