calculated Fields post pivoting ?

[dbrshell]

Hi Guys, is there the posibility to show grand totals for split columns or simple calculated column devision that happen after the aggregation has run in the UI ? For example to backsolve a rate across a time bucket by a devision in hours

[texodus]

You want to see columns for e.g. column paths ["xx", "hours"] and ["xy", "hours"], do I have this right?

[dbrshell]

my question is more general - i.e. how do I do calculated columns after the aggregation.
I am unsure about what a column path is tbh. In my screenshot I would like to see a column 'Volume' which is VolumeMWh divided by hours. For XY-foo this would be 13520MWh / 169h = 80 MW.
In the same Vain I would like to add a Grand Total Column per SplitBy: So for XY this would be 101920 MWh (88400 +13520)

[texodus]

my question is more general - i.e. how do I do calculated columns after the aggregation.

In the same Vain I would like to add a Grand Total Column per SplitBy:

(1) does not enable (2), that's why I asked if what you really want is aggregate rollups over columns (e.g. (2), exactly as we currently do for rows with group_by).

Perspective can calculate column rollups internally, but it is a nasty UX - multiple-level hierarchies on both axes is quite confusing to read and interact with. We may enable this in the future if there is interest but it requires some design work.

Expression columns post-aggregation is on the horizon (a partial PR exists but is bitrotten #2102). This will be implemented in terms of a new operation flatten which is an efficient implementation of exactly what you suggest below, allowing multiple passes over a dataset with the intermediate aggregates only applied at the last step. This is not the same as column rollups (as noted above, we don't even support aggregate-level columns in the UI yet), and only really allows trivial (hierarchy-unaware) column projection.

Even with these restrictions, you would still not want to use these for calculating column aggregates, they will only work for some aggregate types (like sum()) which can be easily decomposed via an expression column, and will be much slower, both because Expression columns are more expensive to calculate, and because you'll need to re-perform the group_by and split_by operations on the flattened Table.

In the meantime, if you are OK with the restrictions of this approach, and with the 1st step of the transform being immutable (as we don't have a UI for flatten-ing in this manner, and instead must be done beforehand in your app code), piping a View into a 2nd Table (as you mention) is not a bad path. Arrow encoding is a few extra data copies, but it is very fast and trivial to implement.

[dbrshell]

One way to solve this is to create another table from a view, but this will increase latency