asked on August 23, 2024
Hi,
In our recent report, we are working with both additive and non-additive metrics across five dimensions.
For example, we have metrics such as price and quantity (additive), as well as unique customers (non-additive), analyzed across the dimensions of country, town, and product type.
In a pivot table report, we need to display a hierarchy with the dimensions country, town, and product type. Subtotals should only be displayed for the additive metrics (price and quantity). The non-additive metric (unique customers) should only be shown when all three dimensions are selected; otherwise, the cell should remain blank.
Could you kindly assist me in preparing an example to implement this in our report?
Thank you for your help.
Best regards,
Janez
⋅ August 27, 2024
Hello, Janez!
Thank you for reaching out to us.
Kindly note that it is possible to remove the text from certain cells on the grid by using our customizeCell() function.
We have prepared a JSFiddle example, where the values for unique users appear only then all the hierarchy is drilled down: https://jsfiddle.net/flexmonster/jyce94un/. You are welcome to update the conditions in the customizeCellFunction to match your use case.
Hope you will find our answer helpful.
Please let us know if you have any further questions.
Kind regards,
Solomiia
⋅ August 28, 2024
Hello, Solomiia,
Thank you very much for providing us with an example that closely aligns with what we aim to achieve.
However, we would like the measure's value to be displayed only when all dimensions are present in either the rows or columns. Specifically, FlexMonster should not perform any aggregation function when a dimension is missing. Instead, it should display a blank value when attempting to aggregate under these conditions.
I've created an example where the current implementation doesn't work as expected. The example works correctly for the "Unique Customer" metric when all dimensions are present.
https://jsfiddle.net/Janez231/3ch56gzd/7/
However, if the dimension "Product Code" is removed at the "City" level, the value should be blank, not a summarized or counted value (e.g., 54).
https://jsfiddle.net/Janez231/3ch56gzd/9/
Could you please help us adjust this behavior?
Best regards,
Janez
⋅ August 28, 2024
Hello, Janez!
Thank you for getting back to us.
We are happy to hear that our suggested solution works well for you. We also wanted to add that by default, Flexmonster aggregates all the fields that are present in the slice.measures. The suggested approach with customizing grid cells does not prevent the field from aggregating but simply hides the unwanted values from the grid visualization.
You can add a condition to customizeCell function to check whether all the necessary fields are present in the data.rows and/or data.columns properties. For example: https://jsfiddle.net/flexmonster/knw25yd0/.
If you are using a multilevel hierarchy, you can check if the levelName corresponds to the highest parent in the hierarchy(Country in our case): https://jsfiddle.net/flexmonster/w09bejdv/.
Please find more details about customizing the grid appearance with customizeCell function in our docs: https://www.flexmonster.com/doc/customizing-grid/.
Hope you will find our answer helpful.
Kind regards,
Solomiia
⋅ August 28, 2024
Hello, Solomiia,
Thank you for your suggestions and examples. However, our reports are highly dynamic, so coding functions for all possible combinations of dimensions would be quite complex.
I would prefer to explore an approach where Flexmonster prevents certain fields from being aggregated.
Do you have a solution or example for preventing aggregation only for one specific measure?
Best regards,
Janez
⋅ August 29, 2024
Hello, Janez!
Thank you for getting back to us.
Kindly note that Flexmonster applies the aggregation function to all the fields that are present in Measures. We understand that saving the lists of fields for dynamic reports can be inconvenient in your use case, but there is no approach to prevent some measures from aggregating.
Our team is wondering if you are using a custom data source API as a datasource in this case.
If so, it is possible to send the empty values for the measure from the server with /select request.
If you are using another type of data source, we recommend focusing on optimizing the customizeCell approach we provided earlier.
Hope it helps.
Kind regards,
Solomiia
⋅ August 29, 2024
Hello Solomiia,
I understand that preventing aggregation on measures is currently not possible, but it would be a great feature to have.
We are using a custom API as our data source, utilizing JSON. Could you help us with an example of how we can address this issue by sending empty values?
Our main challenge is that we want to perform a distinct count of customers, but sending all usernames to Flexmonster is impractical since there are millions of customer records, which would overwhelm the reports. To address this, we already perform aggregation at the API level. However, we need to prevent further aggregation of this measure within Flexmonster.
Thank you,
Janez
⋅ August 30, 2024
Hello, Janez!
Thank you for providing more details about your use case.
Our team is currently working on the example with the custom data source API for the described case. We will be back to you on Monday.
Kind regards,
Solomiia
⋅ September 2, 2024
Hello, Janez!
Thank you for giving us some time.
Kindly note that it is possible to calculate a specific measure only when necessary with the custom data source API. The idea is that using the custom data source API, Flexmonster only displays the values that are sent from the server, so we can send empty values from the server when we don't need them on the client side. You can even add a custom aggregation type to this measure, e.g., "user count".
From our perspective, managing this logic on the server looks as the best solution for the described use case. It would require fewer calculations, and the values would be truly empty on the client side, which ensures the correct behavior of filtering, drilling through, and using conditional formatting.
Please check out a more detailed description of the suggested approach below.
As we've noticed, the aggregated distinct count of "Customers" should be shown only if the nodes in rows/columns are expanded.
So, the idea is that we can send empty data by default, which means even fewer calculations are required.
Then, if the /select request has querytype: expand, check on the server that the fields satisfy the condition(e.g., "Country" or "City" is present in the request), and only then calculate the distinct count of users and send the values to the client side.
We have prepared an example to illustrate the suggested approach: https://jsfiddle.net/flexmonster/3krvtowu/. Please note that we used the pivotEndpoint function to mock the responses from the server for all the requests for the sample.
Hope you will find our answer helpful.
Kind regards,
Solomiia
