Aggr Expression with nested IF

donaniii — Thu, 14 Jun 2018 19:30:16 GMT

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.

topic Aggr Expression with nested IF in QlikView

Aggr Expression with nested IF