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
Quote from: erwink on 11 Jan 2017 05:00:14 AM
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.
I think he needs calculation after aggregation?
Quote from: Invisi on 11 Jan 2017 05:13:55 AM
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.
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.
Quote from: erwink on 11 Jan 2017 05:00:14 AM
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.
Quote from: Invisi on 12 Jan 2017 02:29:54 AM
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 :)
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
okay, now that's easier to understand. Unfortunately, on DMR, I'm out.
(...but that's easy to do on relational! ;))
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.
Thank you Paul. That trick did it !!!