Piechart percentages exceed 100% when the data is 5 vs 11

[virilo_tejedor]

I have a few piecharts in my QlikSense application working perfectly, except for one.

I have created this synthetic example from scratch where I could reproduce the error. QlikSense app is attached.

[MY_DATA]:
LOAD * Inline [

CATEGORY  , AMMOUNT
class_1,    11,
class_2,     5,

];

The result for this input data, it shows percentages of 31.3 and 68.8% that add up to 100.1%

How can I prevent this from happening with this data?

Is it a bug? Is there a workaround?

Thanks!

[Øystein_Kolsrud]

This is actually expected behavior. The sum of the individual percentage components does not always add up to exactly 100%. It can sometimes be a little more, sometimes a little less. The reason is that those individual percentages are rounded off meaning that when you sum them up you can get a slightly skewed result.

In your case you have 16 total values with class_1 having 11 values and class 2 having 5. This would give the following ratios:

class_1: 11/16 = 0.6875 = 68.75%
class_2:  5/16 = 0.3125 = 31.25%

The total of these percentages is 100% as expected, but in your visualization the percentage are rounded off to only one decimal. Both of them are rounded up, so you get class_1 = 68.8% and class_2 = 31.3% and the total becomes 100.1%

[Gabbar]

It will happen as explained by Øystein_Kolsrud  but there is a work around.
Change your measure from Sum(Ammount) to Sum(Ammount)/sum(Total Ammount)
then change your number formatting to double digit percentage values, like this 

then 
go to appearance>presentation> change value labels from auto to custom and select values.

[virilo_tejedor]

Thanks @Øystein_Kolsrud  and @Gabbar !

@Gabbar  your workaround wouldn't work with the next data:

LOAD * Inline [

CATEGORY , AMMOUNT
class_1, 68755,
class_2, 31245,

];

Using this new example, the percentages would be 31,25% vs 68,76%, so we would get a 100.01% sum.

I can't understand why QlikSense doesn't come with a feature like "percentages must sum 100%".  It would be even better if it you can chose the rounded down measure: "round down this feature (when necessary): " class_2

[virilo_tejedor]

I tried to solve it using the next expression:

if (CATEGORY='class_1',
    Num(Sum(AMMOUNT)/Sum(TOTAL AMMOUNT),'##.0000'),
    1- Num(Sum({<CATEGORY={'class_1'}>} AMMOUNT)/Sum(TOTAL AMMOUNT),'##.0000')
)

But it doesn't work because using CATEGORY as a dimension in the pie chart would make the expression "Sum({<CATEGORY={'class_1'}>} AMMOUNT)" to be zero when CATEGORY=class_2

Is there any way to calculate  class_2 percentage as 100-(class_1 percentage)?  

[Øystein_Kolsrud]

Yes, increasing the precision only reduces the effect, it won't eliminate it. You could of course use some ridiculous number of decimals to help you out, but even then you would eventually run into theoretical limits on what decimal precision you can have for a binary encoded double value.

The thing is that there is no way for Qlik Sense to know what the "correct" way of making the different values sum up to 100% would be. This is not really a Qlik issue, but something that you will run into in pretty much any data representation tool. It would be incorrect to display 31.25 rounded down to 31.2 as 5 is rounded up, and it would be equally incorrect to round 68.75 down to 68.7. Both of them individually should be rounded up, and it is when you add up those rounded values that the end result can get confusing.

There's a lot of information and discussions on the topic out on the web. If you search for something like "percentage sum to 100", then you'll get tons of hits.

[Or]

If you want to avoid this issue, the best option is not to use percentages in the first place. If you do need to use percentages, not displaying the number on the chart would be a good idea (it'll still be available as a tooltip, but since you'll only see one at a time, it won't scream out "More than 100%").

Otherwise, you'll have to live with this. It's the same in Excel.

[virilo_tejedor]

Sorry @Øystein_Kolsrud    I didn't found anything regarding Qlik with this search https://www.google.com/search?q=qliksense+%22percentage+sum+to+100%22

I think that enforcing it to sum 100 in detriment of one single variable value (CATEGORY=class_2), and in favour of the rest would be an acceptable approach.

I tried this approach without success, since expressions like "Sum({<CATEGORY={'class_1'}>} AMMOUNT)"  have a value of zero when CATEGORY=class_2, due to CATEGORY is a dimension of this chart

Is there any way to enforce it to sum 100 using QlikSense?

[neerajthakur]

Hi @virilo_tejedor 

Try adding total in your expression. sum(total Amount)

[virilo_tejedor]

This proposal of using TOTAL did not solve the problem

Here you can see a description of the root cause. And I think it only could be solved if, for one dimension, you fit the result to the value of the n-th decimal across the rest of the dimensions.

But I don't think it is possible to check other dimension values in a pie chart expression because it's intended to be zero.

So, I think that the only possible solution would be to use your own pie chart extension that ensures percentages to add up to 100. Or even better, if  Qlik provided us with this feature in its pie chart implementation 😉