Hi all,
As application builders in a commercial/supply chain domain, pivot tables are for us one of the most important and powerful widgets on workshop. However, over the last couple of months we had to get very very creative to enable some requested features, or tell the users they have to live with certain behaviours of the pivot widget.
Since I am very pationate about continues improvement, I curated my list of ideas that would imho unleash the potential of pivot tables and bring them to the next level 
Happy to hear additional thoughts on this from the community and I cross my fingers that someone from Palantir PD sees this 
Also happy to provide more detailed input if needed!
Background: In many use cases we hear users say, they want to “get away from bazillion of excel spread-sheets” - Which is a true statement, but at the same time we have to face the reality that users are so heavily used to the look&feel of the excel world and that changing such user habit is a journey 
And in-fact excel has some neat capabilities, that I would love to get our applications a little closer to!
With that in mind, pivot tables are a great segway for us to create adoption in the “excel-lover” user base as it allows to a) condens a lot of information down to a small real-estate that looks adjacent to excel and b) enables a very intuitive click-where you want to see/do something navigation which excel is in-fact not capable of 
!
Game-Changer Enhancements:
-
Mulit-layers concept to accomodate excel-esque view (and multi OT support): The excel world has created a certain perspective how tables are consumed. Trying to describe it, I would say it is a left-to-right flow per Keyfigure → over time | Next Keyfigure → over time. Here one example.
The current pivot table capabilities make it very tough/impossible to create something similar. For example you have two key-figures “Actuals” and “Open Orders” and your column grouping is the date (past and future). Naturally, it would make sense to calculate and show actuals only in the past and open orders only in the future. But since by default an aggregate is calculated across all column groupings, you will find a lot empty/zero aggregated columns of actuals / open orders unnecessarily taking away precious real-estate.
I know that pivot tables allow function-backed since recently, but still this would not solve all my needs. So my proposal would be to introduce a layer concept as-like xy-charts have it. Concept:- (In an ideal world this layer concept would be possible with multiple object types)
- at the base widget level, you configure the default row/column groupings definition. I see two options for this. Either a shared properties OT is enforced. Or there would be one base OT configured from where the groupings are constructed. It would then be our responsibility as builders to ensure, that the grouping properties exist in all object sets/types.
- Each layer would have:
- object set → again, an ideal world would allow even different OTs, but for the start I would also be super happy with being e.g. able to specify different Object sets of the same type (e.g. one focussing on all previous full years, another only on the current year, or differentiating closed/open orders)
- (optional) filter → If the object set is NOT be part of a layer, at least a filter is needed to allow having a layer specifically focussing on e.g. only the current year / only on open orders
- groupings → by default the main parent groupings are applied. But a layer can overwrite them. This would be used to e.g. say layers “Sales Yearly” and “YoY Growth” have a yearly column grouping, whereby “Sales Quarterly” and “Orders Quarterly” use Year-Quarter.
- aggregates → as they are today (with belows further enhancements )
-
Easy way to use aggregates from other column grouping (i.e. to calculate YoY Growth): Imagine the case your object set holds sales transactions and you would like to create two aggregates “FY Sales” and “YoY Growth”. The FY sales aggregate is easy, you make the sum of value and set the year as column grouping.
But how do you set up the aggregate to calculate the YoY Growth? To my knowledge there is no easy way to do this - you always have to construct creative work-arounds or use the newly added (and costly) function-backed. But why does it have to be so cumbersome? Ideally we could just use the “previous” column grouping value in the aggregation math. What I am looking for is something similar as like on the default sorting definition. There I can specify first/last or a specific value of the column grouping. I would love to have this added within the creation of an aggregate and be able to select the N-previous/ N-next. N - because you might want to reference the previous-previous-previous (e.g. quarter if you have a quarterly view and still want to calculate YoY and not QoQ).
Medium Enhancements - that would still create a big smile on my users face :
- allow variable-backed row / column grouping order and aggregation visibility (which is expanded, hidden etc.): Similar to how we can allow users to select and save the columns of interest in a table, we would like to see the same enabled for pivot tables. This would allow us to develop “boomarking” features where users can customize their preferred appearance of a pivot table and save this for each user individually.
- Make column grouping also expandable/collapsible: Recently the multi-column grouping has been added
. Unfortunately, the column groupings can not be collapsed/expanded as like it is on row groupings. The way I would like to use this would be to set e.g. multiple time dimensions Year → Quarter → Month. Where by default the year is exanded and users can drill into Quarter and Month on demand. - (Alternatively to above) Enable users to switch column grouping granularity on Date properties: On xy-charts users can select, if they want to see year, quarter… or seconds on the axis configurations if the x-axis property is of type date/timestamp. The same I would love to see on pivot tables.
- Enable usage of already existing aggregates within aggregate setup: Just like how I can select an existing aggregate in the conditional formatting, I would like to use the same during the creation of a new aggregate. Example: We sometimes create aggregates being a composition of already defined ones. Imagine you have “Actuals”, “Target” and want to calculate “Gap to target in value” and “Gap to target in %”. When setting up the Gap to target, I need to again, define the exact same steps from Actuals and Target just to substract them at the end. And the same repetition is with the %.
- enable support of “filter value extraction” from pivot table filter: There is a nice feature to extract the user selected values from filter variables. However, this is not working on the filter variables backing a pivot table as found out here.
Mandatory bug-fixes:
- user-defined sorting is resetted with change in object set data: As app developers we can configure the default sorting of the widget. On top, users can change this by applying their custom user-sorting. However, whenever the widget reloads (i.e. page switch or change of data in object set) it re-sets back to the default configuration of the sorting. Pally support said this is due to the way the object set service is working, however, I am just confused why e.g. the selection of expandable/collapsed rows is persisted, but not the sorting
- using “hidden” aggregates in conditional formatting is only working on non-expandable row groupings: You can configure a hidden aggregate and use it within a conditional formatting of another visible/expanded aggregate. However, this is actually only working correctly (meaning, it is only applied) on the default row groupings - not on expandable rows. My workaround at the moment is that I make these aggregates visible but give them a size of 25 (0 looks weird, since the cell itself will never go below 25 width, but the header does…).
I know long post - but I still hope it resonates with some of you!
