Pivot Table Collapsed Totals Incorrect

[newuser]

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?

[newuser]

Attaching QVF

[Vegar]

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.

[newuser]

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.

[miskinmaz]

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

[newuser]

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)

[miskinmaz]

Can you please put up a sample app

[newuser]

The earlier part of the thread should have it.

[newuser]

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?