I'm using Inputfield and Inputsum to allow interactvity in a table chart. Inputsum works at an aggregated level, but I want to use the input value to run a calculation at a detailed record level and then aggregate the output. I am using the Inputfield to allow the end user to change their Minimums.
The example......
I'm using the following table to show the calculation that I'm attempting to perform with minimum being used as the input column.
| ID | Client | Revenue | Discount | Minimum |
|---|
| 1 | A | $100 | 85% | $20 |
| 2 | A | $100 | 75% | $5 |
| 3 | B | $200 | 25% | $35 |
| 4 | B | $300 | 90% | $50 |
| 5 | B | $100 | 50% | $45 |
The formula is sum the greater of the following two
- Discounted Revenue = Revenue - (Revenue * Discount)
- Minimum
For the Chart above the correct sum would total to $295. The calculation is shown below.
IDs 1 and 4 use the minimum since this is the greater of the two numbers
IDs 2,3,5 use the Discounted Revenue since this is the greater of the two numbers
| ID | Client | Revenue | Discount | Minimum | Discounted Revenue | Which is greater? The Discounted Revenue or Min |
|---|
| 1 | A | $100 | 85% | $20 | 100-85 = $15 | $20 |
| 2 | A | $100 | 75% | $5 | 100-75 = $25 | $25 |
| 3 | B | $200 | 25% | $35 | 200-50 = $150 | $150 |
| 4 | B | $300 | 90% | $50 | 300-270 = $30 | $50 |
| 5 | B | $100 | 50% | $45 | 100-50 = $50 | $50 |
| Total | | $800 | | | $270 | $295 |
I keep getting the $270 (shown above) but this is the wrong answer. The correct answer would be the $295 when minimums area accounted for. I've attached a sample app that uses this example.
The key here is that the end user needs to be able to change their minimums. !
