Partial Sums in Pivot Table

[#1]

I have the following expression:

   Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)

This is delivering the cell values
as I need but partial sums are not being calculated correctly

SFT	Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)
A	400
B	480
C	480
Partial Sum	880

As shown in the above example partial sum is calculated as ‘880’
. I want this to be calculated as ‘1360’.

How it is possible??

[MayilVahanan]

HI

Try like this

If(rowno() = 0 or isnull(rowno()),

     Sum({$<DTCDTID = {'*'}>} Total<SDT,SFT,PLID> UTM),

     Sum({$<DTCDTID = {'*'}>} distinct Total<SDT,SFT,PLID> UTM)

)

hope it helps

[#3]

hi

try this

if(dimensionality =0,

sum(aggr(Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM),SFT)),

Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)

)

regards

GERNAN

[#4]

Thanks Gernan, but for some reason it is showing error in expression. It seems like its not registering the 'Dimensionality' within the syntax.

[#5]

Thanks Rama, I tried it but the totals are still the same. It does not add up to the total I need. Please suggest.

[kaushiknsolanki]

Try this expression.

     Sum(aggr(

Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM),SFT))

Regards,

Kaushik Solanki

[#7]

Hi Kaushik,

I tried it but it took off all the data from the cells. I have DTCDID at the top of the pivot table, I dont know if that affects the expression.  Following is the layout , I am talking about.

DTCDTID	CO	MNT
SFT	Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)	Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)
A	400	220
B	480	200
C	480	200
Partial Sum	880	420
Sum should be	1360	620

[kaushiknsolanki]

Hi,

     Include all the dimensions into the aggr list.

     Meaning.

     Say if you have field1,field2,field3 in dimension then your expression should be

Sum(aggr( 

Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM),field1,field2,field3))

Regards,

Kaushik Solanki

[#9]

ok . use dimension to your general total like this

see the underline

if(dimensionality =0,

sum(aggr(Sum({$<DTCDTID = {'*'}>} distinctTotal <SDT,SFT,PLID> UTM),DTCDTID )),

Sum({$<DTCDTID = {'*'}>} distinct Total <SDT,SFT,PLID> UTM)

)

regards

GERNAN