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!

[sunny_talwar]

May be this:

Sum(Aggr(Count(distinct total [MA Exn Employee Name]), YourChartDimension))

[alexpanjhc]

thanks for the solution, but I am still getting 746

[sunny_talwar]

Did you add all the chart dimensions?

Would you be able to share a sample?

[jepeerik]

As far as I can understand it looks like your duplicates appear because some values are classified in more than 1 group (dimension). Could you in your script flag these lines with a 1 for each group it should be in (for each group a different column, a new dimension). After that you can add up the ones in a new column, which gives you the count of groups each line is in.

On the front-end you can use this multiplier in your calculation of the percentage (almost all lines are multiplied by 1, only the duplicates are multiplied by 2 or 3, etc).

This can help you with the correct result and it can be better for performance.

[alexpanjhc]

I used the following for the expression for the 3rd expression in the chart.

Sum(Aggr(Count(distinct total [MA Exn Employee Name]), [MA Exn Classification],[MA Exn Classification ReMapping]))

I am attaching the sample in the original post.

thanks!

[alexpanjhc]

Sorry it took me a while to figure out how to attach.

It is there now.

thanks!

[sunny_talwar · Accepted solution]

May be this:

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

[alexpanjhc]

This is right! How did the total's position changed the numbers?

[sunny_talwar]

It was combination of moving the total as well as removing the 1st dimension from the Aggr() function.