topic Re: Issue with Partial Sum in Pivot Table in QlikView

Issue with Partial Sum in Pivot Table

Wed, 08 Jun 2011 16:25:29 GMT

Hi Everyone,

I ran into another problem related to Pivot table. I assume someone might have

faced similar problem.

In my pivot table, I have correct partial sums, but the individual rows have different

values. It seems some adjustment has taken place among themselves.

For example:

In my actual records,

A(1000) +B(500) +C(1000) +D(1500)= 4000

But in my pivot table its

A(800)+B(500)+C(1000)+D(1700)= 4000, something like this.

Thanks & Regards,

Bikash

Issue with Partial Sum in Pivot Table

mazacini — Wed, 08 Jun 2011 20:52:52 GMT

Do you want to post a screenshot of your Pivot table?

Rgds

Issue with Partial Sum in Pivot Table

Anonymous — Wed, 08 Jun 2011 22:19:18 GMT

If you could attach your .qvw example that would be helpful in order to understand what's going on.

Re: Issue with Partial Sum in Pivot Table

Wed, 08 Jun 2011 22:40:43 GMT

Hi Mazacini,

I have attached the screenshot of the excel file which holds the actual data and a screenshot of what my pivot tables does.

I have also attached the script for the bucketing.

if(num(today()-Date(MODIFIED_DATE))>0 AND num(today()-Date(MODIFIED_DATE))<=90,dual('Current',0),

if(num(today()-Date(MODIFIED_DATE))>90 AND num(today()-Date(MODIFIED_DATE))<=270, dual('>3 Months',1),

if(num(today()-Date(MODIFIED_DATE))>270 AND num(today()-Date(MODIFIED_DATE))<=365,dual('>9 Months',2),

if(num(today()-Date(MODIFIED_DATE))>365 AND num(today()-Date(MODIFIED_DATE))<=730,dual('>12 Months',3),

if(num(today()-Date(MODIFIED_DATE))>730,dual('>24 Months',4),'NULL'))))) as Bucket,

Here the filters are TIER_GEO= APCH & TIER=1.

You can see that the total sum is correct=$ 45,280,645

But if you follow that Pending-> Current->APCH= $35,297,096.

Whereas from the excel(Actual Date) its

Pending-> Current->APCH= $41,970,595.

Thanks a lot,

With best regards,

Bikash

Re: Issue with Partial Sum in Pivot Table

Wed, 08 Jun 2011 22:42:02 GMT

Hi Johannes Sunden,

Thanks for the prompt response.

My application is around 40 MB,

Could you please tell me how can i reduce the data, without affecting my pivot tables' result and then upload it in the

community.

With best regards,

Bikash

Re: Issue with Partial Sum in Pivot Table

Anonymous — Wed, 08 Jun 2011 22:44:29 GMT

Hi Bikash,

Sure, make a selection that limits the data heavily and verify that you still get the unexpected result in your calculation. After that you can go to File > Redue Data > Keep Possible Values, and QlikView will reduce the document and keep your selection and the related values. This should bring the size down significantly if you make a narrow enough selection.

Re: Issue with Partial Sum in Pivot Table

Wed, 08 Jun 2011 23:20:35 GMT

But the process say it will remove the data permanently.

So should I do this and save it with a different name.And then cancel my original applicaiton,

Please suggest,

Thanks Bikash

Re: Issue with Partial Sum in Pivot Table

Anonymous — Wed, 08 Jun 2011 23:23:03 GMT

Right, do a save as and save it as another file.

It doesn't matter too much though since when you reload the document, it will still bring back all the data according to the script.

Re: Issue with Partial Sum in Pivot Table

Thu, 09 Jun 2011 16:22:08 GMT

Hi Johannes,

I am not able to upload my application. Can I please email it to you.

Thanks beforehand,

Bikash