aggr (i think) function question

[ziabobaz]

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.

ID	STATUS	AMOUNT
500	created	100
500	confirmed	100
700	confirmed	200

Thank you!

[sunny_talwar]

May be this

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

[sunny_talwar]

Actually this

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

[sunny_talwar · Accepted solution]

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

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

[swuehl]

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).

[ziabobaz]

great, it works!

[ziabobaz]

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.

[swuehl]

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
500	100

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