I need to summarize a big set of data to show partial and total sum at different level of aggregation. Based on the selection of a customer I have a macro that cycles on possible values of a set of fields, and for each combination extracts and save the value of some expression.
Suppose level1 may assume typea, typeb and typec and level2 subtype1, subtype2, subtype3, subtype4; at the end I have to create for each customer aggregated data like this:
- typea = 100
- typea_ subtype1 = 80
- typea_ subtype2 = 20
- typeb = 120
- typeb_ subtype1 = 40
- typeb_ subtype2 = 15
- typeb_ subtype2 = 55
- typeb_ subtype2 = 10
... and so on
In order to make available this data to a dashboard I've tried using variables; it works, but having 7 level of aggregation the usage of memory is too huge, so I'd like to create a new table having as columns: customer, typea, typeb, typec, typea_ subtype1, and so on.
The question is: is it possible to create a table like that, with fields that don't belong to the original data but are calculated real-time?
