Appian Community and Appian Academy are being upgraded. From July 24–August 3, the Appian Community will be in read-only mode. During this time, the site will be read-only and user registration will be disabled. We apologize for any inconvenience this may cause, but a more secure, stable, and performant Community experience is coming soon!
The new Appian Community launches August 3, followed by Appian Academy on August 7. During the migration, Appian Community Edition, Appian Academy, Documentation, Certifications, Instructor-led Customer Training, Partner Sales Training & Accreditation, and Forum (for Appian Partners and Customers only) will remain available.
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.