Sum of rows into a Text Object - How to use AGGR ?

[#1]

Hello,

I have a straight table (see screenshot). I'm trying to get the sum of rows of the last column (DELTA) into a text object i.e - 38. 

After reading some of the discussion, what I need seems to be the AGGR + MAX functions. After several attempts, I could not get it right.

Here is the formula I use in the column DELTA.

Sum({$<CONTRACT_TYPE=>}([SV COMMITMENT]))

-

If((Sum({$<CONTRACT_TYPE=>}(([YEARLY_SLOT]*vCapacity)/(1+vTAT)))

- (Sum({$<CONTRACT_TYPE={'CSA'}>}[DET SV])))*-1 >0,

(Sum({$<CONTRACT_TYPE=>}(([YEARLY_SLOT]*vCapacity)/(1+vTAT)))

- (Sum({$<CONTRACT_TYPE={'CSA'}>}[DET SV])))*-1,0)

So basically, if my capacity (YEARLY SLOT) + (SV COMMITMENT) is lower than my demand (DET SV), I will have needs.

It's the case for the next 7 years. But after that, my capacity is bigger than my demand, so my need has to be 0 and my delta positive.

- 38 is just the sum of the rows. But I'm trying to get that -38 into a text object.

Can AGGR + MAX functions help ? How ?

Thank you

[effinty2112 · Accepted solution]

Hi Aymeric,

Have you tried this in a listbox?

Sum(Aggr(

Sum({$<CONTRACT_TYPE=>}([SV COMMITMENT]))

-

If((Sum({$<CONTRACT_TYPE=>}(([YEARLY_SLOT]*vCapacity)/(1+vTAT)))

- (Sum({$<CONTRACT_TYPE={'CSA'}>}[DET SV])))*-1 >0,

(Sum({$<CONTRACT_TYPE=>}(([YEARLY_SLOT]*vCapacity)/(1+vTAT)))

- (Sum({$<CONTRACT_TYPE={'CSA'}>}[DET SV])))*-1,0)

,

YEAR))

Regards

Andrew

[#3]

it works fine like this - thanks Andrew