topic Re: How to create a pie chart with positive and negative values in QlikView

How to create a pie chart with positive and negative values

Mon, 16 Apr 2012 11:44:10 GMT

Hi all,

I have to create pie charts of 11 products in 4 different regions, but one of the pie is not visible its showing error "Positive and Negative values in chart" i know that there are some negative values in the data, but can any one tell how to show a pie

any help is appriciated

How to create a pie chart with positive and negative values

christophebrault — Mon, 16 Apr 2012 11:59:49 GMT

Hi,

you certianly have an expression like SUM(YourValue) and a dimension like products or region. You can try an expression like : if(SUM(YourValue)>0,SUM(YourValue)) this will show only positive value.

You can do if(SUM(YourValue)<0,SUM(YourValue)) to show negative in another chart.

If you want to show values whenever they positive or negative, you can use : fabs(SUM(YourValue))

fabs() make values absolute.

Re: How to create a pie chart with positive and negative values

Mon, 16 Apr 2012 13:10:23 GMT

Hey thanks chris, it worked, but why its not showing as negative percentage in the data label

Re: How to create a pie chart with positive and negative values

etay_netintent — Mon, 16 Apr 2012 13:54:31 GMT

I think that the one thing missing in the advice you got was the fact that it is not possible to show negative values in a pie. how would you show it ?? what does a negative slice look like ?? all of the advice you have received was a way to get around this problem by making sure there are no negative values.

Basically the answer to "how can I show negative values in a pie" is... you can not.

Etay Elazar

Re: How to create a pie chart with positive and negative values

jonathandienst — Mon, 16 Apr 2012 15:27:01 GMT

I agree with Etay. Pie charts are not useful for displaying data with a mix of positive and negative numbers. I suggest that you look at some other visualisation of the data.

Regards

Jonathan

Re: How to create a pie chart with positive and negative values

Tue, 17 Apr 2012 07:46:37 GMT

Hi Etay,

thanks alot for your reply, as u said that we can not create a pie with a negative values, i am not sure whether we can create it in QV or not but in normal Excel we can create it, plz see the attached pic

Re: How to create a pie chart with positive and negative values

christophebrault — Tue, 17 Apr 2012 11:54:17 GMT

It's possible in qlikView.

See the attached file.

Re: How to create a pie chart with positive and negative values

CELAMBARASAN — Tue, 17 Apr 2012 12:18:08 GMT

Hi,

You can try expression like this

=if(Sum(FieldName)>0,Sum(FieldName),Num(Fabs(Sum(FieldName)),'-###.##%'))

Celambarasan

Re: How to create a pie chart with positive and negative values

Tue, 17 Apr 2012 12:48:09 GMT

Hi Chris,

I am using a PE version of QV, so can not open your file, it would be great if you can write me back what you have done

Re: How to create a pie chart with positive and negative values

jagannalla — Tue, 17 Apr 2012 12:53:11 GMT

Chris has written like this..

In edit script:

LOAD * INLINE [

Country, Score

US, 4971.765

Japan, -200.941

5 EU, 141.181

RoW, 735.292

];

In front end, Straight table or pic chart

Dimension as Country

Expresssions:

first expression -> =fabs(sum(Score))

second expressino -> =sum(Score)

If you want to see 2nd expr as in % check on Relative box in expression tab.

Re: How to create a pie chart with positive and negative values

Tue, 17 Apr 2012 12:57:22 GMT

Hi Celambarasan,

There is no change in the data values, they still are same its does not show any negative percentage

How to create a pie chart with positive and negative values

CELAMBARASAN — Tue, 17 Apr 2012 13:04:29 GMT

Hi,

How did you calculating percentage?

Celambarasan

How to create a pie chart with positive and negative values

CELAMBARASAN — Tue, 17 Apr 2012 13:08:45 GMT

Hi,

If you used Relative uncheck it.

Use the Below Expression

=if(Sum(FieldName)>0,Num(Sum(FieldName)/Sum(Total FieldName),'###.##%'),Num(Fabs(Sum(FieldName))/Sum(Total FieldName),'-###.##%'))

Re: How to create a pie chart with positive and negative values

Tue, 17 Apr 2012 13:27:48 GMT

Thanks Jagan for your help

Re: How to create a pie chart with positive and negative values

Tue, 17 Apr 2012 13:44:58 GMT

Thanks alot for your efforts Celambarasan now its has worked,

one suggestion you syntax is works well when the expression is direct or small, but if we have a huge calculation in the syntax then its a bit difficult to check all the calculation.

I Really appreciate your help thanks alot

Re: How to create a pie chart with positive and negative values

jonathandienst — Tue, 17 Apr 2012 15:02:57 GMT

Of course you can "force" the display by using absolute values, but this data is misleading.

For example, assume we have 3 groups,

A: 100

B: 200

C: -100

Total: 200

Using absolute values:

A: 100 25%

B: 200 50%

C: 100 25%

Total: 400

A and C are 25% of what? The value of 400 has no meaning in reality, it is purely a mathematical construct. so 25% is also meaningless, as is the pie chart...

This is just my opinion, and this information may be useful in your particular case, but I would caution against this approach as it is arithmetically doubtful. Use it at your peril.

Regards

Jonathan

How to create a pie chart with positive and negative values

etay_netintent — Wed, 18 Apr 2012 09:32:16 GMT

i have been off line for day..... and was amazed to see the lively discussion about this point.

Let me say again - It is not possible to show negative values in a pie. I am trying to say that physically it is not possible to represent a negative slice area.

Showing negative values on a positive slice ?? That is the solution that was given. As has been stated in this thread it is a skewed presentation of the data.

I would be wary of showing negative values on a positive slice area.

If you are happy with the solution - so much better....

How to create a pie chart with positive and negative values

Wed, 18 Apr 2012 11:43:42 GMT

Hi Jonathan,

I really agree with you, what you suggest is correct, even i am looking some other option to represent the data, but the client want it in this fashion

Re: How to create a pie chart with positive and negative values

jonathandienst — Wed, 18 Apr 2012 13:03:32 GMT

If the client is insistent on showing with pie charts, how about splitting the data into positive and negative, and showing it on two pies? eg pie of debits and pie of credits.

The two expressions would then be:

Sum(if (val < 0, val, 0))

Sum(if(val >= 0, val, 0))

Regards

Jonathan

Re: How to create a pie chart with positive and negative values

Wed, 18 Apr 2012 13:12:48 GMT

Hi Etay,

Thatz true we cant dispaly both negative and positive percentage in pie chart, but there is no other way to represent the data except the bar chart, can you think of any other way,