20 Replies Latest reply: Oct 23, 2012 6:00 AM by Leni Balakrishnan

# To show sub total as 100% in pivot table

Hi,

I want to show the values in my pivot table as 100% for each sub total

I need to show my pivot table like below

Cg1Cg2
Expr1

Expr2

Total100%100%
xxxaaa25%50%

bbb25%30%

ccc50%20%

Total100%100%
yyyddd10%60%

eee70%40%

fff20%-

Total100%100%
zzzggg15%90%

hhh75%10%

iii10%-

Now I can show the partial total by doing the settings on Presentation tab.

My concern is I am getting the overall total as 100% but not the each sections total as 100%. The 100% should be shown for all fields in the cyclic groups.

So What should I do to get 100% value in each section??

Can anyone help me in this??

Regards,

Leni

• ###### Re: To show sub total as 100% in pivot table

Hi,

Can u post ur Expr1 & Expr2 ?

Regards

• ###### Re: To show sub total as 100% in pivot table

Sure.

Expr1 = Sum(Amount)/Sum(Total Amount)

Expr2 = sum(Qty)/Sum(Total Qty)

Regards,

Leni

• ###### Re: To show sub total as 100% in pivot table

Hi,

use this :

Expr1 = Sum(Amount)/Sum(Total<Dimension1> Amount)

Expr2 = sum(Qty)/Sum(Total<Dimension1> Qty)

Dimension1 shud be CG1 here

Hope it helps!!!

Regards

• ###### Re: To show sub total as 100% in pivot table

Hi,

Thanks for the expressions.

But I am getting error when I use like sum(Amount)/sum(Total <cg1> amount)

I think we cannot use like SUM(Total <cg1> Amount) like this.

Thanks,

Leni

• ###### Re: To show sub total as 100% in pivot table

Hi,

what are the dimensions of ur cyclic group & r u using the cyclic group directly in ur pivot table, if yes, then it can't show like it is showing in ur question.

Regards

• ###### Re: To show sub total as 100% in pivot table

My Dimensions are cg1 and cg2.

• ###### Re: To show sub total as 100% in pivot table

r u using the cyclic group directly in ur pivot table ??

• ###### Re: To show sub total as 100% in pivot table

Yes.

• ###### Re: To show sub total as 100% in pivot table

Then how can u have the kind of picture of the table you've posted in ur question ?

It shows the dimensions clearly while cyclic group wud show as a group.

• ###### Re: To show sub total as 100% in pivot table

Something like this only I have asked but in each subtotoal I should get 100% value as the sample I have posted earlier.

The cyclic group have a set of dimensions. Based on our need we will choose one dim.

Is this ques right??

Kindly clear me if this is not your question.

Thanks,

Leni

• ###### Re: To show sub total as 100% in pivot table

Hi,

Create a variable Grp1=getcurrentfield(your group name here)

Expr1 = Sum(Amount)/Sum(Total<\$(Grp1)> Amount)

Expr2 = sum(Qty)/Sum(Total<\$(Grp1)> Qty)

Regards

• ###### Re: To show sub total as 100% in pivot table

Could you please explain how this will work??

Thanks,

Leni

• ###### Re: To show sub total as 100% in pivot table

Since we need to have Dim1 in our expression and u're using a cyclic group as ur dimension so you can select any one dimension at one time. In order to have that dimenison and be able to use it in ur expr, we need to get the dimension of the group which you have selected.

Getcurrentfield function returns the currently selected dimension from the group.

Anything else or you have got the result you want ?

Regards

• ###### Re: To show sub total as 100% in pivot table

I got the result as I want.

I would like to know how the given expr is working

I didnt understand this part

Sum(Total<\$(Grp1)> Qty)

Sum(Total<\$(Grp1)> Amount)

How this is working??

Total <\$(Grp1)> Qty

Regards,

Leni

• ###### Re: To show sub total as 100% in pivot table

Gud that u gt d result....

it is quite simple: as soon as u select any dimension from ur cyclic group in ur pivot table then the value returned by the variable Grp1=that dimension(selected by you) & it get placed in ur expr using \$(Grp1).

Remember that when we simply used the dimensions itself instead of the group then we used the Dim directly in our expressions but here we're using Cyclic group so we need the dimension which has been selected or you can say the active dimension.

I hope its clear.

Regards

• ###### Re: To show sub total as 100% in pivot table

First let me convey my Thanks to you.

For helping me in getting the logic and patiently replying for my doubts.

My Actual doubt is something below. I have understood those stuffs (you explained above) but I didnt understand how this sum value gets calculated.

Sum(Total <CustomerName> Amount)

I didnt understand its working.

How it calculates the SUM value.???

I think this might cleared my ques.

Once again thanks for your support

Thanks,

Leni

• ###### Re: To show sub total as 100% in pivot table

Well when u see what its doing, then u'll understand urself what it's doing.

sum(Total<Dim1>Qty) will return the total Qty for each Dim1 value neglecting Dim2 values so in expr ur Denominator returns the total(or Sum) value not for all the values of Dim1 but for each individual value of Dim1.

Regards

• ###### Re: To show sub total as 100% in pivot table

Thanks a lot for your helps

• ###### Re: To show sub total as 100% in pivot table

Try the Aggr() aggregation function:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {,dimension})

"Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides."

You could probably use something like:

Aggr(Sum(Amount)/sum(TOTAL Amount),Cg1)Cg1cg1)Cg1)

See "Sum of Rows in Pivot Tables" section in Reference Manual (ppage 853 in v11.0 Manual).

• ###### Re: To show sub total as 100% in pivot table

Hi,

Go through the diementionality() option available in QlikVIew. I am not sure if this helps for your requirement.

Regards/Hari.