asked on April 21, 2023
Hello Flexmonster !
I'm trying to create a calculated constant, meaning that I want to calculate a sum of a column of all lines and then use this constant to do other calculations.
Here is what i got so far: https://s3-eu-west-1.amazonaws.com/statics.send-up.net/Public/Test/weightsReport.json
The constant I'm trying to get is the total of the POTENTIAL column - in this case is equal to 33 403 401.49
For each operator i want to calculate the Valuation of the product based on the weightOUT and the POTENTIAL. For exemple Anderson and Sons (operator) has weightOUT total equal to 619 303.98 , so the Valuation of the product should be equal to (619 303.98 / 33 403 401.49) * 100 which is equal to 1.85% and not 46.21% as calculated in the pivot.
Is there a solution to my problem ?
Best regards,
Michaela
⋅ April 24, 2023
Hello, Michaela!
Thank you for writing to us.
Flexmonster does not provide a straightforward approach for using the grand totals in calculated values. In the provided example, the Valuation of the product aggregation uses the POTENTIAL value for Anderson and Sons instead of the grand total. Hence, the percentage is calculated as (619 303.98 / 1 340 164.28) * 100, which equals 46.21%.
As a workaround, you could retrieve the total PRODUCT POTENTIAL value using the getData method. This value can be used to add a new calculated measure with addCalculatedMeasure() API call. This would provide the desired result at initial loading, but the formula would not be updated when the total PRODUCT POTENTIAL value is changed due to the report's modifications. You are welcome to check the example: https://jsfiddle.net/flexmonster/u1hcwfq0/
Please let us know if this solution would work for you.
Best Regards,
Maksym
⋅ May 2, 2023
Hello, Michaela!
Hope you are doing well.
We would like to know if you had time to check the provided workaround for using grand totals in calculated values.
Please let us know if this solution worked for you.
Best Regards,
Maksym
⋅ May 16, 2023
Hello, Michaela!
Just checking in to ask if you had time to look at the provided workaround. We would like to know if it works for you.
Looking forward to your reply.
Kind Regards,
Maksym
