Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to get percentages to add up to 100% when dividing by the distinct count

I have an issue with a pivot table where I am counting distinct people and also have a percentage of each category dimension to the total sum.

Due to the distinct count the percentages always add up to over 100%

Here is a simplified example:

DimensionDistinct CountRatio(%)

Group1

660%
Group2440%
Group3220%
TOTAL10100%

The total of ratios will be 120% if added up individually. I understand why this happens and I am wondering if there is a way to work around this.

In the Ratio expression I divide by the distinct total count which causes this.

Dividing by the non-distinct total is not an option since then in each group I would count more people than there actually are. Since there are much nore non distinct records in total then the total percentage would be much less than %100.


So my question is: Is there a dynamic way to divide by the sum of the rows(12 in this case)?

7 Replies
MVP
MVP

Re: How to get percentages to add up to 100% when dividing by the distinct count

You can try an advanced aggregation, like

=sum( aggr( count(DISTINCT ...),Dimension))

Not applicable

Re: How to get percentages to add up to 100% when dividing by the distinct count

Thanks. I considered using aggr but didn't follow up on it since there is more than 1 dimension. Could I use the same method for 2 or 3 dimensions?

MVP
MVP

Re: How to get percentages to add up to 100% when dividing by the distinct count

Should work, just add all your table dimensions to the aggr() function's dimension list:

=sum( aggr( count(DISTINCT ...),Dimension1, Dimension2, Dimension3))

Not applicable

Re: How to get percentages to add up to 100% when dividing by the distinct count

Thanks. When I do a total sum it does kind of work.

The sum of all individual rows do add up to 100%. But now the total percentage is less than 100% and the sum of the subtotal are also less than 100%. The individuals rows do not add up to the subtotals and the subtotals do not add up to the total or to 100%.

MVP
MVP

Re: How to get percentages to add up to 100% when dividing by the distinct count

Try

sum(

     aggr(

          count(distinct Person) / sum(total aggr( count(distinct Person),Dimension1,Dimension2,Dimension3))

     ,Dimension1,Dimension2,Dimension3)

)

Highlighted
Not applicable

Re: How to get percentages to add up to 100% when dividing by the distinct count

That doesn't help either. And it's not possible to fit it along with other functionalities I have in the pivot table.

So I decided to change what I am counting to avoid this problem. Thanks for your help!    

Not applicable

Re: How to get percentages to add up to 100% when dividing by the distinct count

Hi,

Im facing the same problem. I am trying to create a bar graph with the percentage of total number of people who have A's. so i am using the formula:

Count

({$<NewScore={'A'}>} DISTINCT UserName)/Count(DISTINCT UserName)

But the answer is showing more than 100%. Can u please help.