7 Replies Latest reply: Apr 12, 2018 8:49 AM by Stefan Wühl

# aggr (i think) function question

Hi,

I have the following data.

I want to SUM (AMOUNT) only those IDs, which are CONFIRMED and have CREATED status in the given period.

Trying the formula formula (it dosn't solve the problem):

SUM( {<status={'confirmed'}>}  AGGR ( SUM ( {<status={'created'}>} ) AMOUNT), ID ) )

The result should return 100 which is Confirmed amount for Order 500, because it has also Created status in the same period.

IDSTATUSAMOUNT
500created100
500confirmed100
700confirmed200

Thank you!

• ###### Re: aggr (i think) function question

May be this

Sum({<ID = P({<status={'confirmed', 'created'}>})>} AMOUNT)

• ###### Re: aggr (i think) function question

Actually this

Sum({<ID = P({<status = {'confirmed', 'created'}>}), status = {'confirmed'}>} AMOUNT)

• ###### Re: aggr (i think) function question

Ignore the above two expressions... try this...

=Sum({<ID = P({<STATUS = {'confirmed'}>})*P({<STATUS = {'created'}>}), STATUS = {'confirmed'}>} AMOUNT)

• ###### Re: aggr (i think) function question

great, it works!

• ###### Re: aggr (i think) function question

What does your original expression return?

Using Sunny's data model, it does also return 100.

Sunny's expressions should work though (and depending on your data model, you may not even need the last STATUS = {'confirmed'} modifier, i.e. if your AMOUNT is not given per STATUS record, but once per ID).

• ###### Re: aggr (i think) function question

Stefan, my previous expression with AGGR returned nothing, because it was already aggregated by status Created (i.e. confirmed status was excluded)

The last Status=Confirmed should stay, otherwise it will sumup both Confirmed and Created. I only need the former.

• ###### Re: aggr (i think) function question

Using Sunny's QVW and correcting a small typo in your expression (removing a closing parenthesis):

ID =SUM( {<STATUS={'confirmed'}>} AGGR ( SUM ( {<STATUS={'created'}>} AMOUNT), ID ) )
100
500100

edit: I don't want to say that this expression is better than Sunny's. Just to understand why your original is not working.