Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
it works fine like this - thanks Andrew