Problem with Partial sum in Pivot table

[#1]

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.

[trdandamudi · Accepted solution]

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

[#3]

Thanks Thirumala. It worked..Great..

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

[trdandamudi]

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

[#5]

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

[marioglasmv]

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.

[#7]

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

[marioglasmv]

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