Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. 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
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
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