Goal: I'm trying to build a table that gets the spending per each category and subcategory per month, the categories are rows and months are columns. Since the user can change the timeframe I'm using dynamic columns to generate the columns for each month. I'm trying to group by category so it displays the full spending per category and then when the category row is expanded I'm displaying the breakdown of spending per month for each subcategory of that category.
On normal non dynamic columns I can select an aggregation function, but on dynamic rows i cant do that.
Is there a possible way around this to still display the aggregated values in dynamic columns?
For example here December 2024 is a dynamic column. I need to sum up rows in the sub-rows and display it in the same column in the aggregated row of the category.
You do lose a lot of the control over things like aggregation when using dynamic columns (but not all of it -- one feature of the Summary Row add-on to the table is that it will let users select the "Count Unique" aggregation). I don't think there is a clean way to use the table natively to do this, though you could maybe transform your data source for the table to process the sums as part of a broader Category object. Pair this with the current sub-category details as a expandable row component table and you can have the top level aggregation as the main row.
As @pyrrho pointed out, dynamic columns sacrifice standardized methods such as aggregating for the flexibility of being able to handle dynamic data types
I can make a feature request for this but I am not sure it will be prioritized on our roadmap.
My best suggestion is to not use dynamic columns, since the data will be all number values, and find a work around where you can dynamically name the column to the name you want to specify, such as the month in the given example above.
