very very important....how to add a row in pivot table

[#1]

hi

i am creating a pivot table in which i am taking a column from database as row. from database i am getting six different rows but i want to add 2 more rows showing the total of these 8 rows and their count.

how can i do that.....plz help me

[Anonymous]

Vitul, see new attachment in my post above.  I'm not sure you're notified without this additional post.

[#3]

hi thnx for your solution....

ihave 1 more problem in my document....when i am using a calculated dimension...the raanking is done on the basis of a column named membercount and the totals and all others must be calculated on the basis of salary...i am having problem in executing that condition in the expression

plz help asap....

regards

Vitul

[Anonymous]

It is not a problem at all.  You can use rank based on one expression in any other expression.  In general, it may like this (using my earlier example):

if(rowno()>0,
<any expression>,
sum(if(aggr(rank(sum(Measure),4),Dimension)<5, aggr(<any expression>,Dimension),0))
)

[#5]

in my case...ist i am pulling a columnn named provider on the basis of a column called ecount...for it i am using calculated dimension

i.e aggr(if(rank(sum(ecount/3),4)<51,provider),provider)......i need top 50 provider on the basis of ecount....

now in the expression i had to use allowed amount as a measure means i had to sum(amount)...

i am using

if(rowno()>0,sum({<quarter={1},year={'P'},key={'A'}>}amount),sum(if(aggr(rank(sum(ECOUNT/3),4),provider)<51,aggr(

sum({<quarter={1},year={'P'},key={'A'}>}amount),0)))

now the problem is that i want to show the sum of total amount for top 50 as well as all others...where i have used

ECOUNT/3  this part of expression will evaluate amount for top 50...but how to write it???

in your case you were ranking and evaluating the expression on only 1 measure...here first i am ranking on ecount and then evaluating on the basis of amount......

plz help asap....

regards vitul

[#6]

when i am using the expression according to your suggestionits evaluating 0 for the top50......

i am not able to understand it why????

[#7]

hi Michael

there is one more requirement in pivot table....i have to add another row membership  along with top 50 providers,

total for top50,total of all others and total of all....

from the database only top50 are coming....the formula for membership is different.....plz tell me if it is possible in pivot table..if yes...then explain me with an example plz.....

HERE FORMULA TO CALCULATE MEMBERSHIP IS DIFFERENT AND REST OF THE FORMULAS ARE SAME AS WE HAVE DISUSSED IN EARLIER DISCUSSIONS...

HOW TO EVALUATE TWO DIIFERENT FORMULAS IN A SINGLE COLUMN THEN??

[Anonymous]

Vitul,

I don's see how to add another line, except the same way I described earlier.  See charts 5 and 6 in attched - it is a combination of solutions in charts 2 and 4 and 2 and 3.

Regards,

Michael

PS: i hope you have enough information  now to figure out what is possible.

[#9]

hi Michael

i have used your solution in my approach and i am getting desired results......but i am facing one problem with your solution...

the situation is like this...

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt),provider),0)))

this is working fine....

but in some cases i have to divide pd_amnt with another measure count...how can i achieve that in aggr function??/

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt),provider)/sum(count),provider),0)))

i have used it but its not working.

thnx and regards

Vitul

[Anonymous]

Not sure I can get it right without knowing the full picture.  Anyway, my guess is:

sum(if(aggr(rank(sum(amount),4),provider)<11,aggr(sum(pd_amnt)/sum(count),provider),0)))

[#11]

still its not wrking i have tried it.....the report which i am developing in it i have to show the total of top50,total of all other and also total of all of the PD-amnt in the same pivot table....is it fissible in pivot table???