Add a calculated function to a chart

Ashley

Hi,

We’ve heard feedback that many would like to have more flexibility when it comes to building charts and performing calculations in a viz. Tell us what you think of this idea: create an aggregated calculation in a chart that evaluates based on the chart’s grouping dimensions.

For example, let’s assume you want to create a chart showing profit margin. A simple version of this is sum(profit)/sum(sales). Doing this once in a Prepare is easy enough, but you soon realize that for every combination of grouping dimensions in your dataset (maybe 1) month, 2) product, 3) month+product), you need to create a different Flow branch with a Group recipe, followed by a Prepare so that the math for margin works out. Then you realize that if you want to filter out a given SKU or territory, you need to do that upstream...and that's extra work that takes you extra time.

With a user-defined aggregation function, you’d define the profit margin function, sum(profit)/sum(sales), in the charts tab of your dataset and place it onto the chart as if it were a measure. Then you could add any dimension(s) to your chart, and the result would be grouped by those dimensions. Plus, you’d be able to easily apply those filters for those SKU or territories without adding a filter upstream.

If you’re reading this post and think this would be a great capability to add to DSS charts, be sure to upvote it! ( = “yes, please!” ). Feel free to leave a comment in the discussion about how this capability would help you or your team.

Cheers,

Ashley

tgb417

@AshleyW
,

An interesting idea, Depending on the capabilities and ease of use I see that this could reduce the need for third party viz products. Thank You.

Thinking about this idea leads me to wonder about the scope of the availability of these aggregations between users. Do the aggregations live just in a single chart? Do they live in the context of multiple charts in a lab? Do they live in a project for any viz created in that project? Can the aggregations be shared in some way with a data source? How do the aggregations show up in the catalogue?

You are showing a fairly simple aggregation. In your note here? What about more complicated aggregation type. For example multi year, “year to date” style aggregations. Things like medians on a filtered dataset,

Ashley

Hi @tgb417
,

Thanks for the questions! As an initial scope, let's assume that the functions will be available on all charts associated with one dataset and that all of the aggregations that charts currently support could be leveraged in the function definition.

It's good to hear about how you envision this capability evolving over time; we'll make note of it.

Cheers,

Ashley

Marlan

I like the idea. It's not a limitation I've run into with charts often but certainly see the value.

Good point that Tom (@tgb417
) raises about where this lives.

To expand on that and combine it with another idea... I've long thought that Dataset specific charts could be way more flexible and powerful if one had the capability to do the integrated Prepare step like is available in the Lab. I'd think each chart would have its own Prepare step. This would address many more of the limitations I've actually run into.

To combine the ideas then, the "calculated at display time" formula could live in the chart associated Prepare step.

And yes I realize that I could do data preparation as part of the flow and then chart that. However, often what I'm wanting to do is look at the current dataset not some modified version (for understanding, to check accuracy, etc.). Also my flows are often complex enough that adding a bunch of steps just so the chart works is just not worth it. While I'd really prefer not to I do end up moving to different tool (SQL notebook, SQL query tool, Tableau) rather than clutter up my flow.

Marlan

Ashley

Hi,

This idea has been delivered in 12.2, released September 2023. Feel free to learn more about custom aggregations in charts in our reference doc, and give this new capability a try.

Cheers,

Ashley