asked on December 3, 2020
Hi support team,
We are using a simple if-clause similar to
if( sum('Quantity') > 20 , sum('Price'), sum('Price') * 2 )
, but the grand total calculation is wrong.
See your example at https://jsfiddle.net/flexmonster/pwn4tk84/
Grand Total should be 234, but it is 204
Is this something that could be addressed by changing the formula?
Thanks,
Rainer
⋅ December 3, 2020
Hello, Rainer,
Thank you for contacting us.
Our team would like to explain that the grand total of the calculated measure is calculated using the formula as well. It means it is based on the grand totals of the "Quantity" and "Price" fields.
If you want to see the sum of previously calculated values, we suggest enabling the individual property:
{
...
formula: "if(sum('Quantity') > 20, sum('Price'), sum('Price') * 2)",
individual: true
}
We modified the provided JSFiddle to demonstrate this approach: https://jsfiddle.net/flexmonster/pwn4tk84/.
You are welcome to see our documentation dedicated to calculated values to learn more about the individual property: https://www.flexmonster.com/doc/calculated-values/.
Please let us know if it works for you.
Feel free to contact us in case other questions arise.
Kind regards,
Illia
⋅ December 3, 2020
Hi Illia,
thanks for answering. I tried the suggested solution using the individual prop, but that doesn't create the desired result.
Here's the fiddle https://jsfiddle.net/50aL7xvs/8 that demonstrates the issue.
With individual: false the values in the Calculated field are correct. However, Grand Total is not.
With individual: true the values in the Calculated field are wrong.
The provided GrandTotal attachment shows the result with individual: false, which is closer to what we're trying to accomplish. In that case, only the Grand Total must be adjusted.
Thanks,
Rainer
⋅ December 4, 2020
Hi, flexmonster team,
Any update on this one? I'd like to understand if this is an issue with the format of the demo data, the way we configure flexmonster, or if this is a known flexmonster limitation.
Please let me know if you need any additional information.
Kind regards,
Rainer
⋅ December 4, 2020
Hello, Rainer,
Thank you for your feedback.
We want to confirm that individual: true affects the way all values of the measure are calculated.
Currently, this property is the only way to affect the grand total calculation of the calculated value.
Please contact us in case other questions arise.
Best regards,
Illia
⋅ December 4, 2020
Hi Illia,
My question is not about Individual: true|false property, it's how we can create the correct grand total calculation based on the sample data that I've provided.
Please let me know if this a problem with the test data.
If not and it's a known flexmonster limitation, then please let me know what would be necessary to address it. I could then forward the information to our flexmonster contact person.
Kind regards,
Rainer
⋅ December 7, 2020
Hello, Rainer,
Thank you for the feedback.
Currently, the specified formula defines how to calculate the grand total of the calculated measure. It means the grand total is still based on measures used in the formula.
For example, the mentioned JSFiddle uses the following formula: "if(sum('Quantity') > 20, sum('Price'), sum('Price') * 2)".
It means the grand total of the "Formula #1" measure is calculated as shown below:
If the grand total of the "Quantity" measure is greater than 20, then the grand total of the "Price" measure, else the grand total of the "Price" measure multiplied by two: if(41 > 20, 204, 204 * 2) = 204.
This is the default behavior of the grand total for calculated values.
Currently, the only way to change this behavior is to set the individual property to true.
It allows displaying the summarized value (174 + 60 = 234) instead of using the formula. However, this property affects the way all values are calculated.
We agree that the possibility to change the grand total calculation logic sounds reasonable.
Currently, the corresponding task is added to our backlog. Yet, our roadmap is full at the moment, therefore we are not ready to provide you with an exact ETA for this feature.
Our team will keep you updated on this point.
Please contact us if other questions arise.
Regards,
Illia
⋅ December 7, 2020
Hi Illia,
The mentioned fiddle isn't the latest in our thread, please take a look at this one instead: https://jsfiddle.net/50aL7xvs/8
The formula that's used there is simple. If there's a sum("Val1") > 0 take it otherwise take values from sum("Val2")
if( sum("Val1") > 0 , sum("Val1") , sum("Val2") )
What you described seems to be perfectly covered by the default behavior of the grand total for calculated values. The formula produces the correct result at the row level, however, it doesn't at the grand total level.
Within the attached file you can see that in case the sum("Val1") > 0 the value is correctly taken from sum("Val2"). However, the grand total only takes sum("Val1") values into account.
Kind regards,
Rainer
⋅ December 8, 2020
Hello, Rainer,
Thank you for your response.
For the calculated measures, it is expected to have grand totals calculated according to the formula in our current implementation.
On the provided screenshot, the same formula is used to calculate the grand total:
if(sum("Val1") > 0, sum("Val1"), sum("Val2"))
When calculating the grand total, sum("Val1") equals 9999, and sum("Val2") equals 115050.
It means the result will be calculated as shown below:
if(9999 > 0, 9999, 115050) = 9999
Could you please clarify that summing of calculated row values is a preferable behavior for grand totals for your case?
Our team is looking forward to hearing from you.
Regards,
Illia
⋅ December 8, 2020
Hi Illia,
I can see now where the disconnection is.
We would expect the grand total to be the sum of the calculated row values not a calculation in itself. Please let us know if this can be accomplished using flexmonster.
Please see the attached Excel files for details.
Kind regards,
Rainer
⋅ December 9, 2020
Hello, Rainer,
Thank you for the detailed information about the desired results.
Currently, the default behavior of the grand total for calculated values cannot be changed.
Yet, the possibility to calculate the sum of rows in the grand total sounds reasonable.
Therefore, we have added the corresponding task to our backlog.
However, our roadmap is full at the moment. That's why our team is not ready to provide an exact ETA for this feature.
We will keep you posted on this matter.
Feel free to contact us in case further questions occur.
Best regards,
Illia