In some cases it's beneficial to break out of the "row wise" nature of Lens formulas and include all values of a certain metric along some dimensions.
Use case
Some example use cases:
- Calculate a probability distribution - on a histogram the count should be shown as percentage of the total amount of documents per bucket. To do this, the value of each bucket has to be divided by the overall sum of all buckets
- Calculate "divergence from the mean" - subtract the average bucket value from each bucket
- Calculate "percent relative range" - convert the value in each bucket into the percentage of the range of values (highest value is 100%, lowest value is 0%)
This is close to to how the "overall sum/min/max/avg" aggregations in TSVB work.
Grouping
As this calculation is aggregating the values from all buckets of a series, it's necessary to specify which dimension(s) define a series for this calculation. In case of moving_average/cumulative sum etc. we automatically pick all bucket dimension except for the first date column. We can use the same default, but I would suggest we allow the user to configure this, as it's not always the first time dimension which makes sense here. As a side note - it would make sense to allow this for existing classical time series calculations as well (separate issue). To specify this, we would need references (separate issues)
Implementation
I suggest the following syntax:
overall_sum(<metric or math>, group_by=reference(<dimension name>))
We can start with the classical sum/min/max/avg calculations. The overall calculation can be done on top of every raw metric or math node - it behaves very similar to cumulative sum etc. and can also be implemented in the same way (first calculate the nested math/metric column, then execute a special expression function, the calculate the parent math root in which overall_sum is embedded)
References are explained in a separate issue - the group by parameter works like the grouping parameter of existing expression functions for calculations, except it's exposed to the user. We should probably default to the same behavior with the first time bucket dimension, but allow explicitly stating it as well.
I'm not sure whether that's possible at the moment, but we need to allow the same grouped parameter multiple times and turn it into an array to be able to pass multiple group references in. Another syntax for lists would be possible as well, e.g. group_by=list(reference('dim1'), reference('dim2'))
In some cases it's beneficial to break out of the "row wise" nature of Lens formulas and include all values of a certain metric along some dimensions.
Use case
Some example use cases:
This is close to to how the "overall sum/min/max/avg" aggregations in TSVB work.
Grouping
As this calculation is aggregating the values from all buckets of a series, it's necessary to specify which dimension(s) define a series for this calculation. In case of moving_average/cumulative sum etc. we automatically pick all bucket dimension except for the first date column. We can use the same default, but I would suggest we allow the user to configure this, as it's not always the first time dimension which makes sense here. As a side note - it would make sense to allow this for existing classical time series calculations as well (separate issue). To specify this, we would need references (separate issues)
Implementation
I suggest the following syntax:
overall_sum(<metric or math>, group_by=reference(<dimension name>))We can start with the classical sum/min/max/avg calculations. The overall calculation can be done on top of every raw metric or math node - it behaves very similar to cumulative sum etc. and can also be implemented in the same way (first calculate the nested math/metric column, then execute a special expression function, the calculate the parent math root in which overall_sum is embedded)
References are explained in a separate issue - the group by parameter works like the grouping parameter of existing expression functions for calculations, except it's exposed to the user. We should probably default to the same behavior with the first time bucket dimension, but allow explicitly stating it as well.
I'm not sure whether that's possible at the moment, but we need to allow the same grouped parameter multiple times and turn it into an array to be able to pass multiple group references in. Another syntax for lists would be possible as well, e.g.
group_by=list(reference('dim1'), reference('dim2'))