topic Re: Pivot Table Collapsed Totals Incorrect in App Development

Pivot Table Collapsed Totals Incorrect

newuser — Sun, 14 Apr 2019 19:49:28 GMT

In the attached QVF, the pivot table totals are correct when the dimensions are fully expanded. When collapsed, the totals are incorrect. How can this be fixed?

Re: Pivot Table Collapsed Totals Incorrect

newuser — Sun, 14 Apr 2019 19:50:09 GMT

Attaching QVF

Re: Pivot Table Collapsed Totals Incorrect

Vegar — Sun, 14 Apr 2019 21:50:15 GMT

It is your calculated dimension that is causing you trouble. When you start to expand it you make adjustments to which Entity is to be calculated, but before expanding the calculation seem to include all Entities. It disregard your calculated dimension expression.

My guess would be that you need to calculate the higher level dimensions as well OR figure a way to avoid all calculated dimensions in the pivot.

Re: Pivot Table Collapsed Totals Incorrect

newuser — Tue, 16 Apr 2019 21:09:30 GMT

Is there a way to use lower level entities as criteria in high-level entities? I will ideally still need to use a calculated dimension, but it seems Qlik cannot handle using lower-level entity criteria when only the high-level entity is shown. I don't think Dimensionality() will help because I still need to reference the lower-level entity.

What I'm essentially trying to simulate is filtering based on measure.

Re: Pivot Table Collapsed Totals Incorrect

miskinmaz — Wed, 17 Apr 2019 07:36:01 GMT

you can use aggr in the expression to make the total work as sum of all rows

Re: Pivot Table Collapsed Totals Incorrect

newuser — Wed, 17 Apr 2019 12:59:36 GMT

Hi - could you elaborate or show an example of what you mean? I'm already using AGGR in the calculated dimension. Do you mean using AGGR in the measure? If so, how would that look like? Currently the measure labeled %Park shows this:

num(sum({<pay_method2={'Park'}>}totalpmt) / sum(total<Region,Branch,mainagt> totalpmt), '#,##0.00%')

So would the measure with AGGR look something like this below, surrounding the expression with AGGR? That doesn't yield a correct result, so I must be doing something wrong 🙂

AGGR(num(sum({<pay_method2={'Park'}>}totalpmt) / sum(total<Region,Branch,mainagt> totalpmt), '#,##0.00%'), region, branch, mainagt)

Re: Pivot Table Collapsed Totals Incorrect

miskinmaz — Thu, 18 Apr 2019 05:35:06 GMT

Can you please put up a sample app

Re: Pivot Table Collapsed Totals Incorrect

newuser — Thu, 18 Apr 2019 12:39:47 GMT

The earlier part of the thread should have it.

Re: Pivot Table Collapsed Totals Incorrect

newuser — Sat, 20 Apr 2019 18:28:04 GMT

I figured it out changing how I used AGGR. That seemed to fix it. But now I cannot seem to drag-and-drop any object into my sheet. Has anyone run into this?