Is it possible to create a chart (or perform further calculation) based on the aggregate results?

Article Number:040187

The chart and data aggregation feature cannot perform secondary aggregation on already aggregated results. For example, you cannot calculate the proportion of a value to a certain field value that was previously aggregated.

However, if you want to show the total sales amount and the achievement rate on the same screen, you can place fields for the sales amount, target amount, and achievement rate respectively on the form, and then calculate "sum" for the sales amount and the achievement rate and calculate "average" for the target amount.
In this case, we recommend using a table format rather than a bar chart. The significant difference in the number of digits between achievement rates and sales amounts makes it difficult to read the calculated rates on a bar chart.

Setting example

Form settings

Add the following fields to the form of the app:

  • Date field
    • Field name: Date
    • Field code: Date
  • User selection field
    • Field name: Contact Name
    • Field code: Contact_Name
  • Number field
    • Field name: Monthly Target Amount
    • Field code: Monthly_Target_Amount
  • Number field
    • Field name: Sales Amount
    • Field code: Sales_Amount
  • Calculated field
    • Field name: Achievement Rate
    • Field code: Achievement_Rate
    • Formula: Sales_Amount / Monthly_Target_Amount * 100
    • Unit of measure: % (Suffix)

Graph settings

Configure a chart for calculation. You can specify any name for the chart.
Refer to Creating charts on the "App settings" screen.

  • Chart type: Table
  • Group by
    • Level 1: Date, by the month
    • Level 2: Contact Name
  • Function
    • Average: Monthly Target Amount
    • Sum: Sales Amount
    • Sum: Achievement Rate
  • Filter: All records
  • Sort by: Level 1, Ascending

Records to add

Add the following records.

Example
Date Contact Name Monthly Target Amount Sales Amount Achievement Rate
2021-07-01 A 300000 20000 6.67%
2021-07-08 A 300000 15000 5%
2021-07-12 A 300000 30000 10%
2021-07-05 B 250000 10000 4%
2021-07-15 B 250000 50000 20%

Aggregated record data

Check the aggregate result.
Refer to Creating charts

Example
Date (Monthly) Contact Name Average (Monthly Target Amount) Sum (Sales Amount) Sum (Achievement Rate)
2021-07-01 B 250000 60000 24%
2021-07-01 A 300000 65000 21.67%

Screenshot: The sales amount and achievement rate, calculated from the aggregated results, are shown in the chart

In this way, you can show the sales amount and achievement rate on the same screen.