topic Re: Partial Sums in Pivot Table in QlikView

Partial Sums in Pivot Table

Fri, 22 Mar 2013 22:01:26 GMT

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

Re: Partial Sums in Pivot Table

MayilVahanan — Fri, 22 Mar 2013 22:47:38 GMT

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

Re: Partial Sums in Pivot Table

Sat, 23 Mar 2013 04:33:01 GMT

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

Re: Partial Sums in Pivot Table

Mon, 25 Mar 2013 13:21:54 GMT

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

Re: Partial Sums in Pivot Table

Mon, 25 Mar 2013 13:22:46 GMT

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

Re: Partial Sums in Pivot Table

kaushiknsolanki — Mon, 25 Mar 2013 13:25:24 GMT

Try this expression.

Sum(aggr(

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

Regards,

Kaushik Solanki

Re: Partial Sums in Pivot Table

Mon, 25 Mar 2013 13:47:52 GMT

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

MNT

SFT

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

400

220

480

200

480

200

Partial Sum

880

420

Sum should be

1360

620

Re: Partial Sums in Pivot Table

kaushiknsolanki — Tue, 26 Mar 2013 05:37:41 GMT

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

Re: Partial Sums in Pivot Table

Sat, 30 Mar 2013 03:14:53 GMT

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