asked on December 30, 2024
Hello,
I'll try to explain clearly my case :
- My dataset represents a list of responses recorded on sites.
- For each site, I have a daily response target.
- I use a formula to calculate the daily achievement rate of each site by dividing the response count by the expected target.
I'd need to get the same calculation in the grand total,taking into account evry calculated values and not only those from filled cells.
Here is the actual result with in red the desired result:
I reproduced the case on the following fiddle: https://jsfiddle.net/florent_movework/abt0fgv3/4/
Thank you for helping me solving this.
⋅ 7 days ago
Hello, Patrick!
Thank you for writing to us.
Please note that the provided sample shows the correct behavior in Flexmonster, as grand totals column and rows calculate the average using the formula in calculated values. And, since grand total rows account for a set of subtotals, a divisor used in average("Site objective") calculation increases, resulting in a lower value.
If we understood you correctly, you would like to modify the grand totals for the "Objective" to show the sum of values instead of showing an average per all non-zero values. This can be done by multiplying the average("Site objective") on a distinct count of "Site name" and "Day Date":
{
uniqueName: "Objective",
formula: 'if( count("Response Id") , average("Site objective") * distinctcount("Site name") * distinctcount("Day Date") , 0)',
caption: "Objective",
}
This way, grand totals for "Objective" would become a sum of average("Site objective"), as grand total values are composed of different unique values, the amount of which equals a corresponding distinct count value. For other values, these distinct counts would equal 1, as they lay in a crossing between a single "Site name" and "Day Date", resulting in an unaltered result.
Our team has prepared a JSFiddle illustrating this approach: https://jsfiddle.net/flexmonster/mL3br4k9/
Please note that this solution is dataset-specific, relying on fields in columns and rows.
We are looking forward to hearing your feedback.
Best Regards,
Maksym
⋅ 7 days ago
Hello,
Thank you for your answer. I think this is part of the solution but there is still an unexpected behavior. Please check this fiddle: https://jsfiddle.net/cubhmwqy/4/
As you can see, in the objective column for date 2024-12-01, shown values are 2, 2 and 2 for Site 1, Site 2 and Site 3. The expected "Grand total" for this columns would be 2+2+2 = 6, but the shown value is 2.
The same issue is happening on line totals.
⋅ 5 days ago
Hello, Patrick!
Thank you for your reply.
While we understand your perspective, there seems to be a misunderstanding regarding the expected behavior. Allow us to clarify and explain this case.
The condition in the "Objective" formula is based on the original values present in the dataset. In the column for the date "2024-12-01", there are only records with "Site name" equal to "Site 1". Replacing zero values in subtotals with any number will not affect the grand total since they are calculated using the formula, not by summarizing the displayed values. As a result, the grand total row contains the same value as "Site 1" because there are no records for other sites with that date, and the distinct count of "Site name" is 1.
Achieving the behavior when the grand total for 'Objective" is the sum calculated for each "Site name" requires modifying the dataset to store a record with an empty "Response Id" containing every combination of "Site name" and "Day Date". This way, the "Objective" totals would behave as a sum. Please check our JSFiddle that uses additional data records to modify the formula result: https://jsfiddle.net/flexmonster/qbfamct6/
Please let us know if this solution would work for you.
Best Regards,
Maksym
⋅ 4 days ago
Hello Maksym,
I am Florent, I work for Patrick. Thank you for your answer.
We have successfully integrated the suggested solution into our system, however, upon generating all the rows to represent the combination of sites and dates, we've encountered substantial performance degradation and excessive memory consumption within Flexmonster.
Our initial dataset consisted of 185,000 rows, but upon incorporating all the necessary empty rows, the total count soared to 1 million, marking an over 80% increase. Consequently, we are seeking an alternative approach that would eliminate the need to generate these superfluous empty rows.
Could you provide us with any other potential solutions? Alternatively, would it be possible to consider this as a feature request?
Best Regards
⋅ 22 hours ago
Hello, Florent!
Thank you for your reply.
We understand that the suggested approach can cause a significant performance drop for large datasets, as it requires expanding them even further. Yet, currently, it is the only way of achieving the desired result for the "Objective" formula. Implementing this without adding more data records would require adding a new feature, which would allow setting a different formula for grand totals. We have added this feature to our wishlist, and we will reach out to you if there are any updates on this matter.
Please let us know if any other questions arise.
Best Regards,
Maksym
