Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Go through the diementionality() option available in QlikVIew. I am not sure if this helps for your requirement.
Regards/Hari.
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.
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
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
Could you please explain how this will work??
Thanks,
Leni
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
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
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
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
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