topic Re: total in the pivot table in QlikView

total in the pivot table

alexpanjhc — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: total in the pivot table

sunny_talwar — Wed, 23 Mar 2016 18:44:49 GMT

May be this:

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

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 18:57:46 GMT

thanks for the solution, but I am still getting 746

Re: total in the pivot table

sunny_talwar — Wed, 23 Mar 2016 19:00:55 GMT

Did you add all the chart dimensions?

Would you be able to share a sample?

Re: total in the pivot table

jepeerik — Wed, 23 Mar 2016 19:14:58 GMT

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.

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 19:16:59 GMT

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!

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 19:26:41 GMT

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

It is there now.

thanks!

Re: total in the pivot table

sunny_talwar — Wed, 23 Mar 2016 19:31:34 GMT

May be this:

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

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 19:36:28 GMT

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

Re: total in the pivot table

sunny_talwar — Wed, 23 Mar 2016 19:42:10 GMT

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

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 19:44:19 GMT

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()

Re: total in the pivot table

sunny_talwar — Wed, 23 Mar 2016 19:49:13 GMT

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)

Re: total in the pivot table

alexpanjhc — Wed, 23 Mar 2016 20:33:27 GMT

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.