topic Re: Pie chart with number and percent in QlikView

Pie chart with number and percent

Wed, 19 Feb 2014 14:58:56 GMT

I have 2 fields namely _Chart1Expression3 and _Chart1Expression2 that correspond to the metric in integer (Million scaling) and percent format.

I would like to display both of them in the pie chart using the technique below, however the Chart Legend only displays the numeric portion of the Show Numbers in Legend and not the Text Representation. Is there something I am missing?

dual($($(=_Chart1Expression3)) & ' M / ' & $($(=_Chart1Expression2)), $($(=_Chart1Expression3)))

_Chart1Expression3 = Num(SUM(Metric)/1000000, '#,##0')

_Chart1Expression2 = Num(SUM(Metric)/SUM(Total Metric), '#,##0%')

Re: Pie chart with number and percent

MK_QSL — Wed, 19 Feb 2014 15:01:24 GMT

Can you load your sample file?

Re: Re: Pie chart with number and percent

Wed, 19 Feb 2014 15:12:59 GMT

here you go, in the second chart i would like to see the number and percent as in when you hover over the pie slice

Re: Re: Re: Pie chart with number and percent

MK_QSL — Wed, 19 Feb 2014 15:17:58 GMT

Like this?

Re: Pie chart with number and percent

rwunderlich — Wed, 19 Feb 2014 15:18:19 GMT

Add the expression:

NUM(SUM(Expression1)/SUM(Total Expression1), '#,##0%') & ' / ' & Num(SUM(Expression1), '#,##0')

as a second expression and select "Text as Popup".

-Rob

http://masterssummit.com

Re: Re: Re: Pie chart with number and percent

Nicole-Smith — Wed, 19 Feb 2014 15:19:22 GMT

Have a look at the attached .qvw

Mine is a bit different from Rob Wunderlich's as I use the relative check-box to calculate the percentage.

Re: Pie chart with number and percent

Wed, 19 Feb 2014 15:29:46 GMT

I am not looking so much so for the text as popup which I have working already using the Dual but rather looking to fix the legend where the Number and Percent i.e. text representation of the popup is not showing.

I would have expected the Text representation of my expression to show in the legend since by default that is the display value for any dual for eg. in Month, Jan = 1 but we see Jan in display. My legend is incorrectly showing the unformatted SUM(Expression1) instead of 'percent / number'

hope this helps clarify the dilemma

it might just be a bug with 11.2SR5

Re: Pie chart with number and percent

aveeeeeee7en — Wed, 19 Feb 2014 15:30:15 GMT

Dear Amir

See the Attached Qvw of Pie Chart.

It has different Scenarios.

Hope it works fine for you.

Regards

Aviral Nag

Re: Pie chart with number and percent

Wed, 19 Feb 2014 15:34:53 GMT

Your solution on top right is exactly the problem I trying to solve. The legend is not displaying the text value. Also, showing the dimensions in the pie chart are not the ideal solution since my legend is verbose unfortunately and therefore having a separate legend on the right is better

Re: Pie chart with number and percent

MK_QSL — Wed, 19 Feb 2014 15:37:16 GMT

There are three correct solution given.

Re: Pie chart with number and percent

Wed, 19 Feb 2014 15:39:15 GMT

unfortunately they are not. they are workarounds to the problem i have. it seems this might be a bug as Nag Aviral's solution has the same defect as mine and the others are not what I am looking for.

Re: Pie chart with number and percent

rwunderlich — Wed, 19 Feb 2014 15:53:06 GMT

I see I misunderstood the question. I wouldn't agree it's a bug. The property on the Presentation/Legend is "Show Numbers in Legend". It is correctly showing the number value, not the text. Your reference to the Month example was for the legend Label - not the value -- which is correctly displayed as text.

-Rob

Re: Pie chart with number and percent

Wed, 19 Feb 2014 16:01:09 GMT

Well it is a bug in that it will not accept the NUM() function for the formatting even it has to show numeric representation of the data as opposed to text.

Hoep this elaborates better.

Having to show percent on the data point and numbers next to it or in legend affords poor readability.

It seems I will have to resort to Y-value sorted Bar Chart as an alternative to achieve the task. It would have been ideal if the Pie achieved the same result

Re: Re: Pie chart with number and percent

aveeeeeee7en — Wed, 19 Feb 2014 17:14:56 GMT

Please see the Modified Attachment.

Re: Pie chart with number and percent

Wed, 19 Feb 2014 17:18:13 GMT

still not it

Re: Re: Pie chart with number and percent

aveeeeeee7en — Wed, 19 Feb 2014 17:35:10 GMT

What exactly you Want. Be Clear & specific??

Re: Re: Pie chart with number and percent

veidlburkhard — Thu, 06 Mar 2014 14:43:22 GMT

Hi Amir,

what about this solution:

I looked at your test.qvw and found that, when using the dual function you'll get a result only, if in the 'Number' tab the check box 'Expression default' is marked. In this case the 'Show value in Legend' cannot show the result you want to see. So I unchecked the 'Show Legend' in the 'Presentation' tab and added the 'Dim1' field to the dual function. Also you have to check the 'Show Values on Datapoints' in the 'Expression' tab.

See below your modified qvw.

Enjoy

Burkhard

Re: Pie chart with number and percent

Anonymous — Thu, 06 Mar 2014 15:12:34 GMT

the only way you can have two numbers next to legend in a pie chart, in your case, a percent value and the actual number value of the expression next to each other in the legend is by creating a work around. the way i did it on my end for a very similar requirement is create a straight table with the same dimensions and expression as pie chart and align it right next to the legend with no borders. the pie chart legend can show the real numbers and straight table can show percentages or vice versa.

Re: Pie chart with number and percent

Anonymous — Thu, 06 Mar 2014 15:47:10 GMT

like this. since it is a straight table, it will still respond to your selections in pie chart or in the table and stay consistent..

1. in your pie chart, turn show numbers in legend off.

2. create a new straight table with same dimension as pie chart and two expressions - one for number and one for %.

3. in straight table, turn off borders (cell and table).

4. hide dimension column.

5. make sure your fonts are the same as legend and place the table right next to the pie chart legend and you have what you need.

Re: Pie chart with number and percent

francoiscave — Tue, 11 Mar 2014 14:49:55 GMT

Please see the attached file.