calculation on measure problem

[sayanpwc]

Hello,

Facing a problem on calculation on a measure field of a chart that i want to use n different chart.

Find below the scenario.

Here forecast against actual value is calculated where we have segment ,account and month as dimension. The measure field contains both positive and negative value.

Now i want this calculation in a different chart where dimension is only segment. In this chart i want to create two expression for 'Forecast against actual' - sum of that values where values are positive and sum of that values where values are negative. and other is sum of all ( postive and negative). But in this chart ACCOUNT,months will not be there as dimension. only segment is there but i want to do the aggregation on account month level.but not coming. Please help

[ali_hijazi]

since forecast is by month and account then you always need to use aggregation

for positive:

sum(aggr(sum({<forecast={">0"}>} forecast),account,month))

for negative:

sum(aggr(sum({<forecast={"<0"}>} forecast),account,month))

[sayanpwc]

I think you did not get my point. I need to do this calculation in a different chart where account and month not there as a dimension . Just segment is there as dimension. but there it is aggregated on segment level not in account and month level though using aggr on those two fields but as i dont have those dimension available on this chart it is not giving correct result

[jonathandienst · Accepted solution]

If you want the condition to apply to the underlying forecast data, then simply use

positive  - Sum(RangeMax(forecast, 0)) 

negative - Sum(RangeMin(forecast, 0))

If you need the condition to apply to the partially aggregated data as in your picture:

positive -

Sum(Aggr(RangeMax(Sum([FORECAST AGAINST ACTUAL]), 0), SEGMENT, ACCOUNT, MONTHS))

negatve -

Sum(Aggr(RangeMin(Sum([FORECAST AGAINST ACTUAL]), 0), SEGMENT, ACCOUNT, MONTHS))

Aggr() uses all 3 terms, but only SEGMENT needs to be a chart dimension

[Anonymous]

• Since you need to display in a separate chart where only dimension is SEGMENT, you can use below :
  • For positive:

               sum({<forecast={">0"}>} forecast)

• For negative:

               sum({<forecast={"<0"}>} forecast)

• In case you need to display in same chart use below :
  • For positive:

                sum(aggr(sum({<forecast={">0"}>} forecast),account,month))

• For negative:

               sum(aggr(sum({<forecast={"<0"}>} forecast),account,month))

[sayanpwc]

Thanks a lot. It works!!