topic Negative in Pie Chart in QlikView

Negative in Pie Chart

Tue, 16 Feb 2010 19:54:54 GMT

Hi,

It is possible to put in a Pie Chart Positive and Negative values in the same chart?????

I need Help with this plz

Regards

Carlos

Negative in Pie Chart

disqr_rm — Tue, 16 Feb 2010 19:58:26 GMT

No, I don't think this is possible.

Negative in Pie Chart

johnw — Tue, 16 Feb 2010 23:05:52 GMT

How could you even display them? What does a negative slice of pie look like? I think you need a different way of displaying your data if you have both positive and negative values.

Negative in Pie Chart

Tue, 16 Feb 2010 23:14:24 GMT

i was thinking that for example, when you use two dimensions in chart Pie the second dimension appear on the first dimension with smaller slice than the first one, for that reason i thinked it could be possible.

Negative in Pie Chart

johnw — Tue, 16 Feb 2010 23:22:03 GMT

Hmmm, now that you mention it, I suppose that WOULD be a way to show negatives. And I bet you could fake it up like that. For instance, add a calculated dimension with values 'Positive' and 'Negative', and assign it based on the values aggregated by the other dimension. Might need to change the negatives to positive, but you might not. I still think you could make it look better with another type of chart, but it probably COULD be made to work in a pie chart.

Negative in Pie Chart

johnw — Wed, 17 Feb 2010 00:10:08 GMT

Well, it turns out that I HATE the way it looks, but here's kind of what I was thinking...

GAH! Can't attach a file with the new forum.

OK, here's my script:

LOAD
'Company ' & recno() as Customer
,101 - ceil(rand()*201) as Sales
AUTOGENERATE 10
;

Pie chart dimensions are:

=if(aggr(sum(Sales),Customer)<0,'Negative','Positive')
Customer

And expression is:

fabs(sum(Sales))

I hate it, but there's one answer.

Negative in Pie Chart

Wed, 17 Feb 2010 17:16:39 GMT

Hi John, I saw your answer and i putted in my app, i have a question maybe you can help me, i need to put on my application two filters of month and other two filters of year but they have two work independent of each other, do you have an idea to do this plz i need some help with this, i have 2 days now trying to do this my head isnt strong enough.

Regards

Carlos

Negative in Pie Chart

johnw — Wed, 17 Feb 2010 19:29:52 GMT

Well, the simple answer is that you can't have two independent filters for the same data at the same time. But what are you trying to do with your two independent selections? Compare two different time periods in one chart? Like select January 2009 in one filter, February 2010 in the other filter, and compare the sales for both months? Then have a year month field, select both of those, then have a chart with the year month as a dimension. Or create a couple of variables that the user sets. Then in your chart, use set analysis or an if() to match up your real fields to the "selected" values in the variables. Just examples though. I'd need to know what you're trying to accomplish to give more relevant advice.

Negative in Pie Chart

Wed, 17 Feb 2010 19:47:02 GMT

Thank you John, what they want to see are the sales in the both periods of time, i have in my app a simple table chart with the sales and they want to see the sum of the sales in both periods of time. For example:

Sales in January 2009

Sales in February 2010

and it would be the same for a stack bar chart.

Negative in Pie Chart

johnw — Wed, 17 Feb 2010 20:01:39 GMT

If that's the requirement, then I think all you need is a year month field. If you have sum(Sales) in a table, and they select both January 2009 and Feburary 2010, then the table will show sales in January 2009 + sales in February 2010. If you make a stacked bar chart, where year month is the dimension being stacked, then the two segments in the bar will be January 2009 and February 2010.

So maybe all you needed for the two selections to be "independent" is a year month field?

As far as the definition for the year month field, I always make it an actual date, the first day of the month. That makes things convenient since you can use all the date functions on it. So...

date(monthstart(Date),'MMM YYYY') as "Year Month"

Negative in Pie Chart

Wed, 17 Feb 2010 20:39:52 GMT

Can you explain me a little more about the formula? i have a question, as I mentioned i manage the info with the Month Field and Year Field, what are you putting in (Date) inside the formula, actual date? and do you put that formula in the listbox's expression or in the load of a table??

Regards

And sorry for all my questions Im newbie in qlikview

Negative in Pie Chart

Wed, 17 Feb 2010 20:59:01 GMT

Hi,

The Date is the Specific date Ie 17/02/2010, it is best if you add in the script so that it is not calculated every time.

Hope this is of help,

Regards,

Neil

Negative in Pie Chart

johnw — Wed, 17 Feb 2010 21:21:14 GMT

You'd use the formula during the load of your calendar in the script, or wherever you're loading your Month and Year fields. I'm assuming that your calendar has a field Date. In any case, you'd use the same Date field that you're using to establish the Month and Year, assuming you're doing it that way. So you might have something like this:

[Calendar]:
LOAD
Date
,year(Date) as Year
,month(Date) as Month
,date(monthstart(Date),'MMM YYYY') as "Month Year"
;
LOAD DISTINCT Date
RESIDENT [My Data]
;

There are more efficient ways to get the list of distinct dates, but they're also more complicated, so I figure this makes for a better example. I'll also usually generate my calendar for every day in the range, not just for dates for which I have data, but that's more complicated still, so again, trying to keep the example simple.

Negative in Pie Chart

Sat, 27 Feb 2010 00:55:15 GMT

Hi Jhon, i found a way to do this think but it is su much complicate, because what i did was duplicated my rows but with diferent name and it works good, but raise my reload time, can you help me with other way, i can not use the one at the top of this post because actually i dont manage a entire date, i only have months and years and not a entire date, can you help me with this plz?

Regards

Carlos

Negative in Pie Chart

johnw — Sat, 27 Feb 2010 01:08:08 GMT

I'm not sure quite what you're asking. But if you need to make a date from just a month and year, one way is the makedate() function:

date(makedate(Year,Month,1),'MMM YYYY') as "Month Year"

Negative in Pie Chart

Sat, 27 Feb 2010 01:25:34 GMT

Thank you for the answer, it works very good, but now what i need to do is put another "Month Year" that works independent of the first one, it is possible??

Negative in Pie Chart

johnw — Mon, 01 Mar 2010 19:33:50 GMT

What do you mean by independent? If you want it COMPLETELY separate from any of your main data, you can create a calendar of month years with a different name. For instance:

[Separate Calendar]:
LOAD fieldvalue('Month Year',iterno()) as "Separate Month Year"
AUTOGENERATE 1
WHILE len(fieldvalue('Month Year',iterno()))
;

Negative in Pie Chart

Sat, 06 Mar 2010 19:43:45 GMT

Hi John:

Now the problem is diferent, the question is:

How can i do to sum(Sales) on November and December 2009 and January and February 2010 without a YEAR MONTH filter??

Regards

Carlos

Negative in Pie Chart

Wed, 10 Mar 2010 21:23:19 GMT

I have issues with negatives in pie charts adn would simply like the pie chart to just ignore the negative values, is this possible?

Negative in Pie Chart

mike_garcia — Fri, 09 Apr 2010 03:42:12 GMT

As an option for displaying negative values in Pie Charts, I would suggest the Google Charts API, in which (for the Pie Chart) negative values are returned as blank slices. Might be useful for some situatuions.

Here is the link http://code.google.com/apis/chart/docs/gallery/pie_charts.html

Regards,

Mike.