Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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.

Capture_qlik_aggr.JPG

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

1 Solution

Accepted Solutions
Highlighted
Master
Master

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

View solution in original post

2 Replies
Highlighted
Master
Master

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

View solution in original post

Highlighted
Not applicable

it works fine like this - thanks Andrew