- You can set up a filter interaction the crosses object types by using multiple filter list widgets. There are different combinations that can be used to achieve different desired behavior, but the simplest might be a "cascade" where you have a filter list for Object Type A, then apply the output filter variable to create an
[o] Object Type A [filtered] object set variable.
Then you use [o] Object Type A [filtered] as an input to another object set variable and do the search around step to create the object set of Type B that corresponds to the filtered set of Type A.
Then that set of Type B is the input into the second filter list widget, where you can further refine the Type B objects and ultimately have an output object set that reflects the results of both filters.
- This is currently not possible in the Pivot Table widget, though is a feature under active development. In the meantime you can use a Typescript Function to create custom aggregations and display them in a Function-backed chart, which while not the exact same as a pivot table in this context, can still help visualize the derived, aggregated data.
You can also, as you suggest, create an intermediate object type that represents the "aggregated" metrics. Sometimes this is "natural" - for example in the Foundry Reference Project the idea of a flight "Route" is derived by aggregating flight-level information and calculating some metrics that are then easier to work with in some instances than always doing a dynamic calculation by aggregating flight data by destination and origin. In some other cases it's less natural, but can still be necessary if you have more complex calculations or business logic to apply to derive the relevant information.
The limitation here is obviously that pre-calculated metrics aren't as flexible as dynamically calculating the aggregations if you need to provide arbitrary filtering for the user based on the most granular data (i.e. show a chart of the route performance only for flights on this airline, during this time window, with these three aircraft models, etc.
- For high-cardinality pivots, you'll have more success with an intermediate aggregate object type than trying to work with them dynamically. Logically providing any set of the "top" pivoted rows requires calculating the metrics for the entire object set, which for high-cardinality is computationally tough to do in "real time".
In the case where you can filter down proactively to an object set that represents just a few hundred entities (again say filtering down to a set of 1 million flights that represents 100 routes, then pivoting by route_name you should be ok working dynamically, but if you have 1 million flights across 10,000 routes, then you want be able to dynamically pivot to count the number of flights per route then order a pivot table, even if you only want to show the "top 10 routes by flight count").
So again here, some more expressive representations of the data at different levels of aggregation in new, linked object types can help provide the expressiveness necessary to build your visualizations and workflows.
