topic Re: Problem with Partial sum in Pivot table in QlikView

Problem with Partial sum in Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

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.

Please help me on this.

Re: Problem with Partial sum in Pivot table

trdandamudi — Mon, 07 Nov 2016 02:53:32 GMT

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

Re: Problem with Partial sum in Pivot table

Mon, 16 Jan 2017 00:59:55 GMT

Thanks Thirumala. It worked..Great..

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

Re: Problem with Partial sum in Pivot table

trdandamudi — Mon, 16 Jan 2017 01:36:39 GMT

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

Re: Problem with Partial sum in Pivot table

Sun, 05 Mar 2017 04:11:10 GMT

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

Please suggest.

Thanks

Pushkar

Re: Problem with Partial sum in Pivot table

marioglasmv — Sun, 05 Mar 2017 05:39:11 GMT

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

Tablename:

LOAD

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.

Re: Problem with Partial sum in Pivot table

Sun, 05 Mar 2017 09:49:58 GMT

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

Re: Problem with Partial sum in Pivot table

marioglasmv — Sun, 05 Mar 2017 13:31:12 GMT

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