Chart: Enforce negative values to 0

[erwink]

I have a measure with positive and negative values per month. The Aggregation of those is displayed in a chart.

M1: -3
M2: 2
Aggregated = -1

I'd like to force the negative values to 0.

Doing it in the query doesn't work ..or I do not know how. If I force to zero the negative values, the aggregated total gets positive
M1: -3 -> 0
M2: 2
Aggregated= 2

Set the Chart Axis Range to Minimum=0 doesn't impact the chart , the negative value is displayed

Any idea how I could achieve my goal
Thank you
erwin

[MFGF]

I have a measure with positive and negative values per month. The Aggregation of those is displayed in a chart.

M1: -3
M2: 2
Aggregated = -1

I'd like to force the negative values to 0.

Doing it in the query doesn't work ..or I do not know how. If I force to zero the negative values, the aggregated total gets positive
M1: -3 -> 0
M2: 2
Aggregated= 2

Set the Chart Axis Range to Minimum=0 doesn't impact the chart , the negative value is displayed

Any idea how I could achieve my goal
Thank you
erwin

Hi,

Are you using a relational or dimensional package? I'm assuming relational...

You'd need to do this with a query calculation, eg

if ([your measure selected from the package tree] <0) then (0) else ([your measure selected from the package tree])

If you use the measure from the package (as opposed to the query, so you see [package].[query subject].[measure] rather than just [measure] in your expression), the timing is set to calculate before aggregation, which is what you need here...

Cheers!

MF.

[Invisi]

I think he needs calculation after aggregation?

[MFGF]

I think he needs calculation after aggregation?

Wouldn't that mean it would be applied after the -3 and 2 were aggregated together (ie on the -1 resulting)? I thought erwink wanted a result of 2 (ie 0 + 2) rather than 0? Or have I read this backwards and he wants 0 not 2?

Erwink, can you clarify?

MF.

[hespora]

Don't understand it either... what I understood was you want the individual values at 0 if negative, however the aggregation should still be the original value before any replacing - which may or may not make any sense, depending on how you want to visualize that.

[Invisi]

Doing it in the query doesn't work ..or I do not know how. If I force to zero the negative values, the aggregated total gets positive
M1: -3 -> 0
M2: 2
Aggregated= 2

If I read this part, it suggests to me that he wants after auto aggregation. But he best clarifies himself indeed.

[MFGF]

If I read this part, it suggests to me that he wants after auto aggregation. But he best clarifies himself indeed.

I think you're probably right :)

[erwink]

Hi all

Thank you for all your feedback's..and questions

Explaining something by text is always difficult. The values are "Free capacity in hours" and I use DMR. Hierarchy Team/Employee

I have
Employee1 : -3
Employee12: 2

and aggregated on team = -1 but I would like to have ZERO, because the team has no Free Capacity

but with the proposal from MFGF and what I tried
enforcing each single Employee

Employee1 : -3 -> 0
Employee2 : 2
and aggregated= 2 on team what is definitively not the Free Capacity of the Team

Hope I clarified the issue
erwn

[hespora]

okay, now that's easier to understand. Unfortunately, on DMR, I'm out.


(...but that's easy to do on relational! ;))

[CognosPaul]

How are you building the query? Dimensionally or relationally? Do you have detail filters or slicers?

Try creating a new data item that calls the first:

case when [Measure]<0 then 0 else [Measure] end

with the aggregation type set to calculated.

[erwink]

Thank you Paul. That trick did it !!!