Get Free Trial

Formula help: sum("value1" * "value2")

David Frankson 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”) )

3 answers

Tanya Gryshko Tanya Gryshko Flexmonster 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.

David Frankson April 6, 2018

 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.

Tanya Gryshko Tanya Gryshko Flexmonster April 6, 2018

You need to use double quotes around CalcValue1.
Please check your updated sample with added double quotes: Now GPA2 shows results as expected.

Please login or Register to Submit Answer