# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

## Problem with Partial sum in Pivot table

Hi All,

I am facing one issue while performing partial sum on Pivot table.

I am having data in below table:

 Account GLBal PlugBal Name A001 15000 2300 Push A002 16000 2500 Shankar A001 22000 20 Jackson A001 160 500 Sushma

Now I am trying that if GLBal>PlugBal then I want to generate a Flag 'Y' and if no then I don't want that record in Pivot table.

And I want subtotal also based on Account and Name.

Solution is simple that in this scenario last record should not come in pivot table. I am getting the results as I want but when I am applying partial sum for subtotal then it is adding GLBal=160 also. This is incorrect. You can see in Red box that total should be 22000+15000 = 37000. Expression is removing last record from pivot table but it is adding the value in total sum.

I am attaching QVW and excel to see the expression and data.

1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

## Re: Problem with Partial sum in Pivot table

Please see the attached file for the solution. Hope this helps....

7 Replies
Highlighted
Honored Contributor II

## Re: Problem with Partial sum in Pivot table

Please see the attached file for the solution. Hope this helps....

Highlighted
Not applicable

## Re: Problem with Partial sum in Pivot table

Thanks Thirumala. It worked..Great..

Thanks for your help. Sorry for writing after a long time.

Highlighted
Honored Contributor II

## Re: Problem with Partial sum in Pivot table

Awesome....Glad it worked out the way you want it....

Highlighted
Not applicable

## Re: Problem with Partial sum in Pivot table

Hi Thirumala,

I am facing one issue with this partial sum:

Because of using expression for every dimension(Account, Name and GLBalance), performance of this report is very slow. Can't we use simple dimention in place of calculated dimensions??

Thanks

Pushkar

Highlighted
Partner

## Re: Problem with Partial sum in Pivot table

What you can do is create the Flag Y/N in your load script.

Tablename:

Account,

GLBal,

PlugBal,

Name,

IF(GLBal > PlugBal, 'Y', 'N') AS Flag

FROM

.....;

Then use set analysis in your expression like SUM({<Flag = {'Y'}>} GLBal) so you can avoid you calculated dimensions. It will work a lot faster.

Highlighted
Not applicable

## Re: Problem with Partial sum in Pivot table

Hi Mario,

My situation is little different now. I can't define flag in script. Actually i am calculating flag now based on user input. I have one input box at UI where user is entering a no. Based on that value i am writing the condition for flag that if PlugBal<"eValue" then Y else N.

Thanks

Pushkar

Partner

## Re: Problem with Partial sum in Pivot table

You have to solve it in the expression and not in the dimension, that is what makes it slow.

Try the following expression:

SUM({<PlugBal = {"<=\$(=\$(eValue))"}>} GLBal)

Make sure you get rid off the calculated dimensions with the aggregations and use the regular fieldname only

Gr. Mario