You have some nice data to display into a pivot table, but you can’t rank or format them so you are stuck ? Don’t worry!
We are currently working on the pivot table to include theses features (among others!), until then, here is a workaround using the flat table. And even more, you will learn new awesome stuff using the DataTransform feature.
For instance, let’s take a simple example with a table which has 2 columns : [Car Name] and [Quantity]. You want to display the sum of [Quantity] over [Car Name]. Basically in pure SQL it’s : sum(quantity) .. group by [Car Name] but this reduces the row count from 1000 to 5 for instance (if you have 5 cars), thus, this is not useable in DataTransform where it can just add/remove columns, ‘group by’ is not available.
What if we create a new column which is the sum of Quantity for each [Car Name] first ? Do you now the SQL “over” function ? This allows you to use aggregations that are computed for each row.
sum([Quantity]) over(partition by [Car Name])
The computed column looks like :
We can see that each value is repeated for each [Car Name]. (13, 6, and 8).
This is not yet useful in a flat table because of those repeated values. Let’s find a way to take only the first one. Because you can’t just remove rows in datatransform, we need to find a way to remove them using a filter on the dashboard instead.
Let’s create a new column that will help us to identify the first one for each group. To do that, we use generate a simple auto-incremented value (1, 2, .. ) and we will be able to just take the sum(Quantity) where this column equals 1 (each group will have its own sequence from 1 to n).
row_number() over(partition by [Car Name] order by [Car Name])
The preview looks like :
We can distinguish 5 groups : Alfa Romeo, AMC Hornet, Aston Martin, Bentley, BMW, and for each rownumber = 1, the sum of quantity : 13, 6, 8, 11, 8 (the sum is computed on the preview values only, not on the overall. It will be when you will process the transform). Thus, we can create our flat table from that and use ranking, filtering, format and so on.
Et voilà ! Happy Dashboarding !
ClicData Coder 