total in the pivot table

[alexpanjhc]

Hi Experts

I have a total in pivot table( see attached) and I have some questions.

There are some duplication within this pivot table, however, Qlik is smart enough to delete the duplication. I want to keep it.

So if you add the sub total which is 26+725+1=752, but not the grand total 746.

Thus when I do the percentage, it is still give me non-dups. my percentage is more than 100%

the expressions are

indiv w/ Exception: count(distinct [MA Exn Employee Name])

% of Total: count(distinct [MA Exn Employee Name])/count(distinct total [MA Exn Employee Name])  (the part in red should be 752)

How to resolve this issue?

thanks!

[alexpanjhc]

yes,  I tried two dimensions before. I tried one by one separately. As a matter of fact, I never used 2 dimensions in an aggr()

[sunny_talwar]

I think the issue is related to DISTINCTiness needed for the sum is not at the lowest level, but at the Sub-Total level and that is why the Aggr() with two dimension did not work.

Sum(TOTAL Aggr(Count(DISTINCT [MA Exn Employee Name]), [MA Exn Classification ReMapping]))

The above gives the Sum of distinct count at the most lowest level (756 vs 752)

[alexpanjhc]

that would be ideal.

However, the user wants to have the duplication and non-duplicated numbers.

Someone started this application before me and I am changing based on the original codes.