topic Re: To show sub total as 100% in pivot table in QlikView

To show sub total as 100% in pivot table

Anonymous — Tue, 23 Oct 2012 06:09:06 GMT

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

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

Tue, 23 Oct 2012 06:25:05 GMT

Hi,

Can u post ur Expr1 & Expr2 ?

Regards

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

Anonymous — Tue, 23 Oct 2012 06:43:44 GMT

Sure.

Please find the expression below:

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

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

Regards,

Leni

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

Tue, 23 Oct 2012 07:00:15 GMT

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

Anonymous — Tue, 23 Oct 2012 07:00:19 GMT

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

Anonymous — Tue, 23 Oct 2012 07:11:58 GMT

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

Tue, 23 Oct 2012 07:15:59 GMT

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

Anonymous — Tue, 23 Oct 2012 07:24:23 GMT

My Dimensions are cg1 and cg2.

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

Tue, 23 Oct 2012 07:25:13 GMT

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

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

Anonymous — Tue, 23 Oct 2012 07:30:13 GMT

Yes.

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

konda_haribabu — Tue, 23 Oct 2012 07:38:27 GMT

Hi,

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

Regards/Hari.

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

Tue, 23 Oct 2012 07:52:39 GMT

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

Anonymous — Tue, 23 Oct 2012 08:24:35 GMT

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

Tue, 23 Oct 2012 08:45:38 GMT

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

Anonymous — Tue, 23 Oct 2012 09:00:23 GMT

Could you please explain how this will work??

Thanks,

Leni

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

Tue, 23 Oct 2012 09:05:12 GMT

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

Anonymous — Tue, 23 Oct 2012 09:11:18 GMT

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

Tue, 23 Oct 2012 09:19:52 GMT

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

Anonymous — Tue, 23 Oct 2012 09:32:55 GMT

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

Tue, 23 Oct 2012 09:57:04 GMT

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