[From Wrike] Sorting Pivot Tables in Wrike Analyze
Update: sorting in pivots can be customized now for metrics! If some rows are aggregated, the system will ask you how you want your values to be sorted:
Hello Community!
It’s Herine the Analytics Subject Matter Expert from Wrike Support 😼 You might remember me from my previous posts on financial fields and custom fields.
Today I’ll explain the functionality of Pivot widgets and will cover one of the top pivot questions we get in Wrike Support : why doesn’t sorting work?
One of the main topics we get asked about when it comes to Analyze is Sorting. Today, we’ll look at this in more detail so keep reading 👇
Introduction: Pivots and Tables
Let’s get started by looking at widgets. When two widgets look similar, how do you decide which one to use?
In a nutshell, there are only dimensions (rows/categories) in Table widgets. That means it’s not possible to create formulas in Table widgets which is the main distinction between pivots and tables.
Pivots are more flexible and they have three sections: Rows, Values, and Columns.
In contrast to Table widgets, it’s also possible to create formulas in Pivots, that’s what the “Values” section is for. The section “Column” can be used to break down a formula in Values:
Note: Table “Columns” and Pivot “Columns” are different things. Table “Columns” are more similar to “Rows” in Pivots - it’s the only section available in this widget type. Pivot “Columns” are more similar to “Break by” in charts and can be applied only to formulas under “Values”. Since formulas return only numeric values, non-numeric data (dates, text, etc.) cannot be broken down by the “Column” section.
Why can’t I sort a Pivot widget?
Another exception to Pivot widgets is aggregation. You might have already noticed from my screenshots that the project name “Tarkin Initiative” appeared three times in the Table widget while the Pivot shows it just once and aggregates other rows under it.
If there’s no aggregation in a widget at all, you’ll be able to sort a Pivot by any column. In my example below, there’s just one row for each Project Name, no rows are aggregated, and that allows me to sort the widget by Due Date:
However, once I add Project Owner to this widget, there will be three rows grouped for one of the projects, and sorting by Due Date will have no visual result anymore:
It may seem that sorting isn’t working here, but stick with us :) Once something that activates aggregation is added, the system starts viewing every set of columns as a block. When I’m trying to sort the widget by Due Date, it won’t sort anything to the left of that column:
There is almost nothing to sort since there is always just one due date, so it looks like sorting isn’t working. This will be easier to show on the column Project Owner. Since it has aggregated rows, I can change their order within their small aggregated block, but not the order of the projects:
Alternatively, if I didn’t need formulas in my widget, I could just use the Table widget type. It has no aggregation, which means all the data is simply duplicated in every row. As a result, I can sort this by any column:
I hope this article gave you clarity on the differences between pivots and tables! Please let us know if you have any questions in the comments below.
Herine Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Herine Wrike Team member Become a Wrike expert with Wrike Discover
Thanks for the information. I have struggled with how sorting works with Pivot tables, so this helps me and our team.
I don't have analyze but it is great to understand how it works and the capabilities.
Great to have insights on how Wrike Analyze & Sorting is set up with pivot tables, thanks for sharing!
Thanks for this. That was reallynecessary to better understand it.
This is very helpful. I've been trying to begin using the pivot tables and analyze features our team has but noticed that I couldn't always sort. We've had some complaints about people thinking they broke the pivot tables. Now we know! Thank you so much!
Thanks for explaining different types of widgets - now I will be more likely to try to use one in Wrike!
We don't have analyze but this gives great insight of the capabilities and added value
Pivot lacking very essential feature that it can't display status in the value field (only numeric or date value). Example: I was not able to have the task status of each task (tasks defined as column) against each project (row). i.e. Could not display the Task status as a value, Only mathematical or date functions.
Hi Ali Alemadi, thank you for your comment. I've shared it with our Support Team so that one of our experts can provide you with an accurate reply 👍