Is it possible to create a chart (or perform further calculation) based on the aggregate results?
You cannot perform further calculation on aggregate results using the data aggregation / chart feature. For example, you cannot further calculate the proportion of a value to a certain field value based on the aggregate result.
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, it is recommended that you select Table instead of Bar chart for a chart type. This is because it is going to be difficult to view the calculated achievement rate on the chart when the number of digits is hugely different between the calculated values in the achievement rate and the sales amount fields.
Examples of configurations are as follows.
First, place the following fields on the form of the app.
| Field Name | Field Code | Field Type |
|---|---|---|
| Date | Date | Date |
| Contact Name | Contact_Name | User selection |
| Monthly Target Amount | Monthly_Target_Amount | Number |
| Sales Amount | Sales_Amount | Number | Achievement Rate | Achievement_Rate | Calculation - Formula: Sales_Amount / Monthly_Target_Amount * 100 - Unit of measure: % (Suffix) |
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" |
Add the following records.
| 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% |
Check the aggregate result.
Refer to Creating Charts
| Date (by the month) | 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% |
In this way, you can show the sales amount and achievement rate on the same screen.