Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ziabobaz
Contributor II

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!

1 Solution

Accepted Solutions

Re: aggr (i think) function question

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

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


Capture.PNG

7 Replies

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)


Capture.PNG

MVP
MVP

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

ziabobaz
Contributor II

Re: aggr (i think) function question

great, it works!

ziabobaz
Contributor II

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.

MVP
MVP

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.

Community Browser