I'm working with Time series data where i have hourly values (input energy, produced electricity...)
initially i have generated an SQL Model which pivots me the Data, that i have the nice structure:
Timestamp, Sensor 1, Sensor 2, ...
Now i would like to define the calculation for the Non-additive Measures (e.g. efficiency) It works well in the Chart with the visual query builder, by defining the user defined column AFTER the aggregation.
Issues:
after adding the user defined field, i loose the Option to change the aggregation (Hour / day / month,...) directly in the chart.
additonally, i woudl like to define the calculation rather officially, once...
I thought, this should be the use case for the "Metrics".
But there i have the issue, that i need to define the Aggregation (e.g. sum / average) what is not correct for my non-additive Measures. Trying to type simply wihthout sum / average give the error "no aggregation found, use sum ... or own metrics"
By definition, non-additive measures aren’t aggregated with the base facts/dimensions. As you found, you need to arrange the data the way you want, then apply the non-additive measures.
The last error message you’re giving sounds like you’re trying to use a pivot table. A pivot table is going to be the wrong visualization if you’re mixing additive and non-additive metrics.
I haven’t used the Metrics feature, not sure I can be much help there.
If you can provide some examples or mockups of what you want, that might help me understand what your challenges are.
Thanks for looking at! Basicaly i would like to define the Step "C" inside the Metric. In oder to have to reusable for various charts. Inside the Model i can't put it, as it's the "non-additive" measure.
Not sure, if Metabase "Metrics" can handle this (and are intendet for it), or if I'm on the wrong track there...
Did a quick swing through Metrics and they’re pre-defined aggregations, so they must use some aggregate function (sum, average, etc.). That’s what the error message is trying to tell you.
Your first screenshot defines that metric by diving the sum of the values, so that works properly in the summation stage. In the second, you’re trying to divide the column values in an aggregate and that won’t work; it’d have to be a custom column that you then apply a summation to. (In that sense, you’re turning your non-additive metric into an additive one, but you don’t have to add them necessarily.)
Ultimately, what function to you want to apply to that metric? Do you want its values summed, averaged, ?
As I wanted to calculate non-additive values, I can't use either the sum or the average. If the metric always requires aggregation, I believe i can't use it like this.
So i fear, i have to stick to the process in screnshot 1...
Because, the other option, to do the calculation in the SQL model after aggregation (hourly values to Daily / monthly...) would be quite complex and would loose the ability to switch the aggregation intervall in the chart as well.
Recent version of Metabase added setting the grouping interval as a SQL variable/dashboard filter. You can use that to set the grouping of the source subquery.
