I'm trying to get a sum total of check amounts for a specific account within a date range. This query locates the correct 'checkAmt' values but I want one total - not 5. I get a series of values:
Received: [startIndex=1, batchSize=-1, sort=[field=checkTotal, ascending=true], totalCount=9, data=[checkTotal:2]; [checkTotal:11]; [checkTotal:22]; [checkTotal:33]; [checkTotal:40]; [checkTotal:65]; [checkTotal:96]; [checkTotal:216]; [checkTotal:452], identifiers=]
I want to JUST get 937 (the sum of all 'checkAmt' values)
a!queryEntity( entity: cons!ABC_CHECK_DS, query: a!query( aggregation: a!queryAggregation( aggregationColumns: { a!queryAggregationColumn( field: "checkAmt", alias: "checkTotal", aggregationFunction: "SUM", isGrouping: true ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1 ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "accountNum", operator: "=", value: ri!accountNum ), a!queryFilter( field: "fiId", operator: "=", value: ri!fiId ), a!queryFilter( field: "effectiveDateTime", operator: "BETWEEN", value: {todatetime(ri!startDate), todatetime(ri!endDate)} ) } ) ))
Discussion posts and replies are publicly visible
Hi
You can do
sum( a!queryEntity(YOUR QUERY HERE).data.checkTotal )
Sounded like a good idea, but I get this error now;
Expression evaluation error at function 'sum': Could not cast from DataSubset to Number (Decimal). Details: CastInvalidCould not cast from DataSubset to Number (Decimal). Details: CastInvalid
Shouldn't the aggregation on the field with 'Sum' do that?
Did you do .data.checkTotal?
If the error says could not cast form DataSubset, I think the query was only indexed by data.
This is the result of your query: [startIndex=1, batchSize=-1, sort=[field=checkTotal, ascending=true], totalCount=9, data=[checkTotal:2]; [checkTotal:11]; [checkTotal:22]; [checkTotal:33]; [checkTotal:40]; [checkTotal:65]; [checkTotal:96]; [checkTotal:216]; [checkTotal:452], identifiers=]
.data would give you type DataSubset and .data.checkTotal would give you a list of number that you can then take the sum of
That worked! I did have .data.checkTotal - but I added it outside the sum() vs inside the last ).
Thank you!
Awesome!
When creating a rule using a!queryEntity - should it be saved as an expression rule or interface rule?
Definitely as an Expression Rule.