topic Re: Transforming integer to percentage in pie chart in QlikView

Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 15:40:51 GMT

Hello everyone,

I am trying to convert number to percentage in a pie chart, which i have 3 expressions:

sum([Baby Boomers])

sum(X)

sum(Y)

In the Expression tab, when i select the relative box, all results are 100%:

If i unselect the relative box and set the percentage in the Number tab, the results are wrong:

Below is the chart with the original numbers:

How can i convert the numbers above to the correct percentage?

Regards

Re: Transforming integer to percentage in pie chart

Mark_Little — Thu, 26 Nov 2015 15:46:07 GMT

I would look at something like

sum([Baby Boomers]) / (sum([Baby Boomers]) + sum(X) + sum(Y))

sum(X) / (sum([Baby Boomers]) + sum(X) + sum(Y))

sum(Y) / (sum([Baby Boomers]) + sum(X) + sum(Y))

Mark

Re: Transforming integer to percentage in pie chart

buzzy996 — Thu, 26 Nov 2015 15:59:23 GMT

can u share sample .qvw?

Re: Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 16:03:57 GMT

Thanks for your response Mark.

I have other pie chart, with different expressions:

sum({<[Generations]={"Regional"}>}[Baby Boomers])

sum({<[Generations]={"Regional"}>}X)

sum({<[Generations]={"Regional"}>}Y)

I am just selecting values from Regional for each Generation.

How it should be the expression to calculate the percentage in that case?

Best regards,

Re: Transforming integer to percentage in pie chart

Marcio_Campestrini — Thu, 26 Nov 2015 16:15:34 GMT

Rodrigo

You can calculate it or simply mark the option "Relative" in expressions tab.

Re: Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 16:28:20 GMT

Hi Márcio,

When i select the option Relative in expressions tab, the result is wrong, all values is 100% as i mentioned before:

Re: Transforming integer to percentage in pie chart

settu_periasamy — Thu, 26 Nov 2015 16:42:00 GMT

Hi Rodrigo,

May be try this expression..

sum({<[Generations]={"Regional"}>}[Baby Boomers])/sum({<[Generations]={"Regional"}>} TOTAL [Baby Boomers])

sum({<[Generations]={"Regional"}>}X)/sum({<[Generations]={"Regional"}>} TOTAL X)

sum({<[Generations]={"Regional"}>}Y)/sum({<[Generations]={"Regional"}>} TOTAL Y)

And, set the percentage in the Number tab..

Re: Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 17:17:45 GMT

Hi settu_periasamy,

Unfortunately the expression you suggested didn´t work.

It displayed all values with 100%:

Re: Transforming integer to percentage in pie chart

swuehl — Thu, 26 Nov 2015 17:23:57 GMT

Look at the alternative solution I suggested here:

Using multiple fields in a pie chart

i.e. use a dimension with one expression instead of three expression with no dimension. Then you can simply check the 'relative' option.

Re: Transforming integer to percentage in pie chart

settu_periasamy — Thu, 26 Nov 2015 17:24:10 GMT

Hi Rodrigo,

Just to confirm,

Did you uncheck relative and set "Fixed to" and "Show in percentage" in Number Tab.

Or,

=Num(YourExpression,'#.##0%') -> Expression Default in Number Tab

Re: Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 17:30:32 GMT

Rodrigo,

try this:

sum({<[Generations]={"Regional"}>}[Baby Boomers]) / sum({<[Generations]={"Regional"}>}[Baby Boomers] + X + Y

sum({<[Generations]={"Regional"}>}) / sum({<[Generations]={"Regional"}>}[Baby Boomers] + X + Y))

sum({<[Generations]={"Regional"}>}) / sum({<[Generations]={"Regional"}>}[Baby Boomers] + X + Y)

Re: Transforming integer to percentage in pie chart

Thu, 26 Nov 2015 17:42:15 GMT

Yes settu,

I unchecked relative and set "Fixed to" and "Show in percentage" in Number tab.

Re: Transforming integer to percentage in pie chart

MarcoWedel — Thu, 26 Nov 2015 23:37:48 GMT

Hi,

one solution could be either:

(Rangesum() instead of just adding the fields for proper null-value results)

or (without changing your data model) using a calculated dimension and a single expression to be able to use the "relative" option for expressions like already suggested by swuehl‌ (although I think his CrossTable solution results in the better data model for your requirements):

hope this helps

regards

Marco