Qlik Community

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:

   

AccountGLBalPlugBalName
A001150002300Push
A002160002500Shankar
A0012200020Jackson
A001160500Sushma

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.

1 Solution

Accepted Solutions
Highlighted
trdandamudi
Honored Contributor II

Re: Problem with Partial sum in Pivot table

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

Pivot_01.jpg

View solution in original post

7 Replies
Highlighted
trdandamudi
Honored Contributor II

Re: Problem with Partial sum in Pivot table

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

Pivot_01.jpg

View solution in original post

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

Please suggest.

Thanks

Pushkar

Highlighted
Partner
Partner

Re: Problem with Partial sum in Pivot table

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.

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.

Could you please suggest now?

Thanks

Pushkar

Partner
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