To show sub total as 100% in pivot table

[Anonymous]

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

Cg1	Cg2	Expr1	Expr2
	Total	100%	100%
xxx	aaa	25%	50%
	bbb	25%	30%
	ccc	50%	20%
	Total	100%	100%
yyy	ddd	10%	60%
	eee	70%	40%
	fff	20%	-
	Total	100%	100%
zzz	ggg	15%	90%
	hhh	75%	10%
	iii	10%	-

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??

Thanks in Advance

Regards,

Leni

[konda_haribabu]

Hi,

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

Regards/Hari.

[#3]

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.

[Anonymous]

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

[#5 · Accepted solution]

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

[Anonymous]

Could you please explain how this will work??

Thanks,

Leni

[#7]

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

[Anonymous]

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

[#9]

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

[Anonymous]

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

[#11]

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