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.
Hello April Schuppel is the only way to get one result back when aggregating using sum is by using the sum() function?