Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
donaniii
Partner - Contributor
Partner - Contributor

Aggr Expression with nested IF

Greetings,  I am looking for comments regarding this usage of Aggr.  This expression is being used with QuickBooks Advanced Reporting.  The goal is to list open sales orders.  I am unable to modify the load script. 


The sales order, [Transactions.Ref Number], has both header and line level records.  If the header record fields, [Transactions.Fully Invoiced] or [Transactions.Manually Closed] =0, then sum [Transactions.Amount] for the [Transactions.Ref Number] when the line is not [Transactions.Manually Closed].

This expression is working, but I am not confident this is a proper usage of Aggr.

=if(aggr(sum({<[Transactions.Txn Type]={'Sales Order'}>}[Transactions.Fully Invoiced]),[Transactions.Ref Number],[Transactions.Txn Type])=0,

  if(aggr(sum({<[Transactions.Txn Type]={'Sales Order'},[Transactions.Header Or Line]={'Header'}>}[Transactions.Manually Closed]),[Transactions.Ref Number],[Transactions.Txn Type])=0,

sum(

{<[Transactions.Txn Type]={'Sales Order'},[Transactions.Manually Closed]={0},[Transactions.Header Or Line]={'Line'}>}

[Transactions.Amount]

)

))

Thank you for your comment.

0 Replies