asked on February 12, 2025
Hello,
We are considering adding a pivot table to our data platform, and are exploring using Flexmonster to add that functionality. While Flexmonster looks great, we’ve run into a potential blocker: support for aggregating non-additive and non-mutually exclusive percentage metrics.Our platform reports purchase behavior data that is already pre-aggregated.
For example, rather than reporting a list of purchases for a given person, we report the average amount a coca-cola buyer spends on coca-cola in a year. For an additive metric, such as total sales for coca-cola, we can easily pivot the data and add it up to determine sales for different column or row breakouts. Unfortunately, we have many metrics that are not additive and thus can’t be aggregated based on simple math and whatever row and column selections a user makes.For example, purchase frequency - ie on average how frequently something is purchased in a given time frame - isn’t something you can add up.
Another example is household penetration (HHP) - ie the % of households that bought something in a given timeframe.If the pivot table row selections are retailer & brand, and then the brand is removed from the row selection there is no formula we can use to calculate the HHP for the retailer based on what the HHP was for each brand within the retailer. We need to tell the pivot table what the correct HHP is for the retailer. We can provide the data, but we’re unsure if Flexmonster can be configured to grab the right data point.
Is there any way to support these non-additive and non-mutually exclusive percentage formulas with Flexmonster?
⋅ February 14, 2025
Hello, Zahra!
Thank you for reaching out to us.
Could you please provide us with more details about the case, e.g. the sample data, formulas, and the desired outcomes? With this information, we'll be able to advise you better.
Since your data is already pre-aggregated, our team is also wondering which data source you are planning to use when connecting to Flexmonster. You can find the list of all available data sources for reference here: https://www.flexmonster.com/doc/supported-data-sources/.
Looking forward to hearing your response.
Kind regards,
Solomiia
⋅ February 14, 2025
Hey Solomiia, we would prefer to provide the data via a JSON file. We have some flexibility with how we provide the data and I've provided a small sample of how the data could look below. In the example, "hhp" is a non-additive metric that is already aggregated for the brand, retailer, and brand & retailer. There is no formula we can provide flexmonster to calculate hhp for possible combinations of breaking out the data by brand, retailer, or brand and retailer. The only option I see is for us to provide the data for each possible combination and for Flexmonster to select the right metric from the provided data based on the combination of rows & columns.Here's an example to help illustrate what we're looking for. Imagine a user selects the following for their pivot table: rows (retailer then brand), columns (none), filters (none), values(hhp), options (show subtotals for rows). Based on those selections, the table should return 3 rows with the following dataWalmart, null, 0.18 <-- this is the subtotal line
Walmart, Haagen-Dazs, 0.07
Walmart, Ben & Jerry's, 0.13Is something like this possible with Flexmonster? Example JSON below:
{
"jsonData": [
{
"brand": {type: "string"},
"retailer": {type: "string"},
"hhp": {type: "number"}
},
{
"brand": "Haagen-Dazs",
"retailer": "Walmart",
"hhp": 0.07
},
{
"brand": "Ben & Jerry's",
"retailer": "Walmart",
"hhp": 0.13
},
{
"brand": "Haagen-Dazs",
"hhp": 0.18
},
{
"brand": "Ben & Jerry's",
"hhp": 0.22
},
{
"retailer": "Walmart",
"hhp": 0.18
}
]
}
Thanks!
⋅ 4 days ago
Hello, Zahra!
Thank you for providing us with more details about your use case.
Our team recommends checking out our custom data source API approach for the described case. Custom data source API is the communication protocol between Flexmonster and your own server implementation, using which you can tell Flexmonster which values to show for totals, subtotals, and regular cells as all aggregation logic is located on the server side. If you already have the server that aggregates the data, the idea is to add a couple of endpoints for Flexmonster to it.
You are welcome to check out our introduction page, which tells more about the custom data source API approach, and the step-to-step implementation guide with the detailed code samples.
Please let us know if the described approach works for your case.
Looking forward to hearing from you.
Kind regards,
Solomiia