Formula help: sum("value1" * "value2")
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”) )
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.
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)
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.
So what am I doing wrong here:
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.
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.