Need to be able to calculate the total of column data in order to be used in reporting metrics

Question

For the following table of records, I need to be able to calculate the total of the highlighted columns, (Bid Amount & Principle Balance) so that I can utilize that information to create a list of metrics listed below the Picture.

Balance Underwritten
 
 Sum of Principle Balance

Balance Bid
 
 Sum of Principal Balance (Where Bid Submitted? = Yes)

Balance Won
 
 Sum of Principal Balance (where Bid Won? = Yes)

Bid to Underwritten %
 
 Balance Bid divided by Balance Underwritten

Win to Bid %
 
 Balance Won divided by Balance Bid

Win to Underwritten %
 
 Balance Won divided by Balance Underwritten

Mike Schmitt · Accepted Answer

cls5469 said: this following query which that kind of gives me the result I want 
 1) store the result of that query in a local variable 
 2) call sum() on that result (or the relevant field) 
 
 First you need to know how to access the field in RecordType data post querying. Kinda-stupidly, this requires referencing the entire recordtype and then the relevant fieldname. For the recordtype here in my example I have a field name called "dataLength". (Querying just a page of 10 only for simplicity's sake). 
 So let's display the full value first: 
 Traditional queries would let us just add ".dataLength" after that to get the property alone as a list. Let's try: ( Womp, womp... ) 
 So how do we access it? Following the error message, we add the record type property reference in square brackets: Voila! 
 Then for the cherry on top, since now we just have an array of integers, we can pass that quite cleanly into sum() . 
 Though I often prefer to abstract the individual steps into their own local variables, which adds a bit more flexibility... 
 And as with most things, when crafting expressions it's best to start with something small and working, and then iterate it step-by-step.

amiteshsin · Answer

The variable you are using to hold the grid data use that same variable in the sum function and for referencing the field to be used inside sum we use recordtype!recordName.fields.columnName Once you type recordtype! you will get the necessary fields. (The above solution is when you are using records to fetch data, in case using cdt use index function inside sum instead index(arraywithdata,"columnName",{})

Mathieu Drouin · Answer

You'd have to retrieve the info in a query (separate from the grid) and put it in a local variable. 
 You would put whereever you need to display it. In a grid field sourced by Record Data it's a little tricky. I would suggest creating a small area under your grid to display the sums/balances etc. 
 Something that looks like this maybe? 
 docs.appian.com/.../data-value-display.html

Mathieu Drouin · Answer

a!localVariables(
 local!pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 20),
 local!balanceSum: a!refreshVariable(
 value: a!queryRecordType(
 recordType: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan',
 fields: a!aggregationFields(
 groupings: a!grouping(
 field: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{776544e7-09aa-4079-b038-f3e274675ab5}id',
 alias: "id"
 ),
 measures: a!measure(
 field: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{02381417-037d-4868-bb86-0d812cc564f8}principalBalance',
 alias: "principalBalanceSum",
 function: "SUM"
 )
 ),
 pagingInfo: a!pagingInfo(
 startIndex: local!pagingInfo.startIndex,
 batchSize: local!pagingInfo.batchSize
 )
 ).data,
 refreshOnVarChange: local!pagingInfo
 ),
 {
 a!gridField(
 data: a!recordData(
 recordType: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan'
 ),
 columns: {
 a!gridColumn(
 label: "Loan",
 value: fv!row['recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{1a71e16c-85b9-4882-8ab0-4af845f27271}loanName']
 ),
 a!gridColumn(
 label: "Balance",
 value: fv!row['recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{02381417-037d-4868-bb86-0d812cc564f8}principalBalance']
 )
 },
 pageSize: 20,
 pagingSaveInto: { a!save(local!pagingInfo, fv!pagingInfo) },
 showSearchBox: false,
 showRefreshButton: false
 ),
 a!richTextDisplayField(
 value: {
 a!richTextItem(text: "Total Balance: ", style: "STRONG"),
 a!richTextItem(
 text: {
 a!currency(
 value: sum(local!balanceSum.principalBalanceSum),
 isoCode: "USD"
 )
 }
 )
 },
 align: "RIGHT"
 )
 }
) 
 Not sure if this is what you are trying to do. This example works with paging but doesn't support searching/sorting etc but it gives you an idea. 
 If you wanted filters and such, you'll have to build them out yourself, you can't use the ones provided by recordData if you want sums and totals.

Need to be able to calculate the total of column data in order to be used in reporting metrics

Top Replies