Negative values in pie chart

[Former Member]

I have a kind of an odd problem involving pie charts on DeskI R2.

I want my pie chart to display the breakdown different employment charges by their type: salary, bonus, employment tax, overtime, etc. I created the corresponding dimension and measure, and it works fine.

BUT...

From time to time, one of the totals for a type can be negative, due to a correction of a value from a previous month. That, logically enough, throws the pie chart calculation off. It seems that DeskI uses the absolute value in the pie chart, which results in values that are incomprehensible to the user. What I'd like to do instead is exclude any sectors that have negative values from the chart entirely (and eventually add a note at the bottom listing the values that have been excluded).

Unfortunately, I can't seem to build a filter to do this, since filters cannot accept aggregate function calculations.

I've been turning the problem around and can't seem to come up with a workaround. Has anyone else run across a problem like this before?

[Former Member]

Emily,

The trick here is to 'fool' BusinessObjects.

Let's simplify the situation and suppose you only pull the salary as a measure and 2 dimension objects for aggregation (say: L1 and L2).

Create a pie-chart based the L1 object and the salary and all L1 objects will show even when the aggregated value is negative.

To exclude all L1 objects with a negative value do the following:

1. Create a measure variable 'Salary L1' with the formula:

=<Salary> In <L1>

2. Create a dimension variable 'vCheck' with the formula:

=If <Salary L1> < 0 Then "NO SHOW" Else "SHOW"

go back to the definition tab and make sure that the radio-box for dimension object is selected.

3. Create a filter for the pie-chart. Goto the Menu 'Format' --> 'Filter' and select object <L1> to put a filter on.

Click on the 'Define' button and add the formula:

=<vCheck>="SHOW"

This shouldn't make any difference to the pie-chart.

However there is one more change to make.

4. Update the formula for <Salary L1> and change it to

 =Sum(<Salary>) In <L1>

All the <L1> objects with a negative aggregate value should now disappear from the pie-char.

The type of the object <vCheck> will now have changed from dimension object to a measure and if you try to modify the filter you will get the message that you cannot use aggregates in complex filters.

But that does not matter, because the filter will work.

Hope this will get you started.

Cheers,

Harry

[Former Member]

Harry,

Thank you very much! I ultimately found an answer, putting the test directly in the measure (now a variable based on the original object) in the following manner:

=If (Sum(<My Value>) > 0) Then <My Value> Else 0

DeskI eliminates 0 totals, so this worked without a filter, in the end. The key was putting it in a measure, not a dimension object, as you suggested.

(Seems obvious to me now, but somehow I couldn't see it at first!)