asked on April 5, 2018
Through trial and error I've been unable to get a formula equivalent to sum("value1" * "value2") to work. Is this possible, or do I have to do the multiplication in my data first and then just sum it? Does each value in a formula need to be wrapped in its own aggregate? Ultimately I want something like average( (sum("value" * "weight") / sum("weight")) + sum("bonus" * "weight") )
⋅ April 6, 2018
Hello, David,
Thank you for posting a question to our Support forum.
It is possible to get a formula equivalent to sum("value1" * "value2") to work.
When creating calculated formula you can specify individual property. It allows calculating the formula using raw values. Please check JSFiddle sample.
Here formula sum('Price') * sum('Amount') from the line 36 will be calculated as following:
if you set individual: true: 174 * 36 + 225 * 44
if you set individual: false: (174 + 225) * (36 + 44)
To set individual: true in the UI, you need to select 'Calculate individual values' checkbox.
To perform more complex calculations, i.e. sum("value" * "weight") / sum("weight") , you may need to create two calculated values. One calculated value to get CalcValue1 = sum("value" * "weight"), where you set individual: true. And another calculated value would be CalcValue1 / sum("weight"). Each value in a formula needs to be wrapped in its own aggregate, except for calculated values. Please keep in mind, that aggregations are not applied to calculated values.
Please let me know if you have more questions.
Regards,
Tanya
⋅ April 6, 2018
So what am I doing wrong here:
https://jsfiddle.net/dfrankson/hffddkzq/12/
The first calc uses pre multiplied fields in the source, the second calc is the individual calc you mentioned and it works, but the CalcValue1 / sum("weight") equivalent doesn't seem to work.
⋅ April 6, 2018
David,
You need to use double quotes around CalcValue1.
Please check your updated sample with added double quotes: https://jsfiddle.net/flexmonster/hffddkzq/24/. Now GPA2 shows results as expected.
Regards,
Tanya
