Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

Calculated values that depend on other calculated values throw errors when inactive

Answered
david asked on April 16, 2020

Hi,
 
We recently upgraded to the newest version of Flexmonster. Afterward, some of the report objects that we’d saved in our database started throwing errors when loaded into the new version of the component.
 
The issue appears to be related to calculated fields. When a calculated field uses another calculated field in its formula,  the dependent fields must be active on the report. Otherwise, the fields disappear and errors are thrown.
 
For example, we had a report that had a calculated field called “Last Month Active Clients.” This field was calculated with the formula distinctcount("lm_customer_name"). We also had two other fields, Clients distinctcount("client_id") and New Clients distinctcount("client_wins").
 
Only Clients and New Clients were active on the report.
 
We had another calculated value called “Client Retention” that referenced all three values. The formula was:
(Clients - New Clients / Last Month Active Clients)
 
“Last Month Active Clients” did not appear on the report at all. Until we upgraded the component, this field worked just fine. However, after upgrading, “Client Retention” disappeared from the report and we got the following error message:
 
>  Wrong formula format. Please check.
>  The field with ‘Last Month Active Clients’ uniqueName is not defined.
 
Adding “Last Month Active Clients” back to the report as an active value stops “Client Retention” from disappearing.
 
It seems like the upgrade changed how the component deals with calculated values, and I wanted to make you guys aware of this behavior.
 
Thanks,
David

5 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 17, 2020

Hello, David,
 
Thank you for writing to us.
 
Our team would like to kindly inform you that we did not manage to reproduce the issue on our side.
You are welcome to check out an example we have prepared trying to reproduce the described problem.
 
In case it does not helps, we would like to ask you to provide us with a sample where the issue would be reproducible or modify the example mentioned above in an appropriate way.
 
Also, we suggest considering the usage of the validateFormulas property of the options object.
It indicates whether the validation is performed for calculated values (true) or not (false). In case the validation is turned on and the report contains invalid formula, the “Wrong formula format” alert message is shown. To turn off the “Wrong formula format” alert message, set the validateFormulas property to false. Default value: true.
More information about the options object and its properties can be found in our documentation.
 
Finally, our team would like to kindly notice that it is possible to set the different aggregation for the chosen hierarchy without the necessity to create an additional calculated value. In case several aggregations are needed to be displayed at the same time, the measure can be simply included in the slice, as shown below:

"measures": [{
"uniqueName": "client_id",
"caption": "Clients",
"aggregation": "sum"
},
{
"uniqueName": "client_id",
"caption": "Clients",
"aggregation": "distinctcount"
}
]

 
We are looking forward to hearing from you.
 
Kind regards,
Illia

Public
david April 17, 2020

Hi Illia,
 
Thank you for the response.
 
I did some more digging and I can reproduce the problem on the fiddle.
 
https://jsfiddle.net/0y2g7hv1/1/
 
The way we save reports includes running .getReport({withDefaults:true}) and then saving that into our database.
 
I’ve added a function to the fiddle that does that and am able to reproduce the behavior.

function reloadReport() {
pivot.setReport( pivot.getReport({withDefaults: true}))
}

 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 21, 2020

Hello, David,
 
Thank you for providing us with a sample.
 
We have managed to reproduce the described behavior. The fix for the problem is going to be provided with a minor update ETA May 18.
Our team will keep you posted with any updates on this point.
 
Also, we would like to kindly notice that all saved reports are going to stay invalid after the fix due to the fact they do not include appropriate measures.
Please let us know in case of further questions.
 
Best regards,
Illia

Public
david April 22, 2020

Thanks, I look forward to the update.
 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster May 20, 2020

Hello, David,
 
We are pleased to inform you that the issue with saving inactive calculated values to the Report Object was fixed.
 
This is included in the 2.8.7 version of Flexmonster: https://www.flexmonster.com/release-notes/.
 
You are welcome to update the component.
Here is our updating to the latest version guide for assistance: https://www.flexmonster.com/doc/updating-to-the-latest-version/ .
 
Please let us know if everything works fine for you.
 
Best regards,
Illia

Please login or Register to Submit Answer