topic Re: showing values in % in QlikView

showing values in %

Fri, 20 Dec 2013 17:37:48 GMT

Hi,

i have a stack bar chart wherein the values are alreday converted and displayed in $,mm Format and finally shown without the M symbol (eg: 1500).

To explain things more clearly, i have 4 regions on x-axis and there are designations grouped as stacked.

For the first bar, the numbers shown for the first region (Eg: Asia) are as follows:

Director: 1500

VP: 1300

AVP: 800

svp: 500

i want to convert them in % so that the total of it should sum up t0 100%.

For director it should show 37% (1500/4100)

For VP it should show 32% and so on

Hope the explanation is clear enough

Really looking forward for a solution to this since i have my data always in million dollar and would like to show convert them into % format even while using a pie chart.

Thanks

Re: showing values in %

swuehl — Fri, 20 Dec 2013 17:42:09 GMT

Use the TOTAL qualifier in your denominator aggregation:

=sum(Sales) / sum(TOTAL<Region> Sales)

where Region is your first dimension field and Sales the field you aggregate.

Then use the number tab of the chart properties to format as percentage.

Re: showing values in %

stevedark — Fri, 20 Dec 2013 17:43:42 GMT

Hi Yousef,

Typically you would do that with the TOTAL function, so if your expression is sum(Value) percentage would be:

sum(Value) / sum(TOTAL Value)

Where the TOTAL causes the divisor to calculate across all dimensions. If you have two dimensions in your chart (say in a Pivot) you can do the total across a second dimension:

sum(Value) / sum(TOTAL <Dimension> Value)

If you want to ignore selections on a certain field (so you always look at the total, not the total of selected items) you can use set analysis to ignore some selections in the total:

sum(Value) / sum({<Dimension=}>} TOTAL Value)

Hope that helps,

Steve

Re: showing values in %

Fri, 20 Dec 2013 17:46:57 GMT

Hi,

Chart Properties ->Expressions Enable the Relative Check Box .

Re: showing values in %

Fri, 20 Dec 2013 18:04:18 GMT

Thanks for the promt reply. This worked. The only problem that i face is that the data labels are missing in some places. For eg: if there are 4 desgignation for a region, it shows data labels only for 3 designations.

Any idea why that is happening. On mouse over, it shows the value as 8% but that is not displayed on the bar.

Thanks

Re: showing values in %

preminqlik — Fri, 20 Dec 2013 18:27:07 GMT

use relative in chart proprties > expression tab

Re: showing values in %

stevedark — Fri, 20 Dec 2013 18:35:37 GMT

HI Yousuf,

Can you upload a screenshot of this? Or if you have dummy data in there the document itself.

Cheers,

Steve

Re: showing values in %

nico_ilog — Tue, 01 Dec 2015 13:22:50 GMT

Excellent. What a great help!

Re: showing values in %

Anil_Babu_Samineni — Sun, 29 May 2016 08:59:05 GMT

Hello Yousuf,

Did you get any solution for this. Because, I am having same issue...