# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. \$300 savings extended to February 9th Learn More
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
Honored Contributor II

## Re: Problem with Partial sum in Pivot table

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

7 Replies
Honored Contributor II

## Re: Problem with Partial sum in Pivot table

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

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.

Honored Contributor II

## Re: Problem with Partial sum in Pivot table

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

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

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.

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.

Could you please suggest now?

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