Regions: CAN, US, MEX
DESIRED OUTPUT: Want the columns to display as US, CAN, MEX
But the Pivot table auto sorts it to CAN, MEX, US
I did try the "Preserve the original sorting order" under the options tab of the Pivot tab and that didn't work either
Hi @Dolly Kirubavathi ,
The Pivot Table macro sorts your items alphabetically, but it seems that your need your own custom sorting. So, here is a workaround - I'm not sure if you have Server or Cloud, so for the first option just nest your macros directly and for the second option use the Table Toolbox macro (other steps are the same).
Use the Table Transformer macro to assign additional numbers to your items:
T1.'Region' LIKE "US"
T1.'Region' LIKE "Can"
FROM T* ORDER BY 'Status'
And then use the Table Filter to hide this additional 'Status' column if necessary:
Here we've checked the Hide Columns option in the Filters tab and chosen which column to hide in the Filtration tab -> Set filters' default values menu:
Hope this helps your case.
The solution you have suggested is exactly how I had manipulated the sorting order up till using the Table transformer. I then use the Pivot table to read this table transformer data and display it. I have not used a table filter.
Please advise if I have to use the Table Filter to achieve my goal here?
Here we used the Table Filter macro only to hide the additional column with numbers used for sorting.
The standard sorting of the Table Filter macro allows you to sort into ascending or descending order - I see that it is not the case, cause you want to display US, Canada and Mexico (not the standard C-M-U or U-M-C sortings).
But the output desired is the Pivot table format, for the markets to be displayed as Columns with Vehicle info as Rows and the number of Vehicles in that market as "Calculated Column".
If I wrap the table transformer on top of the Pivot will I still get the same output?
I currently have this: table transformer with my desired output wrapped in a pivot table
Yes, of course - the Table Transformer macro sees output of the Pivot Table macro as a regular table, it won't ruin anything.
I mean that you can create different combinations with our macros - they work with the previous level only.
For example, you have a big manually created table with, let's say, 100 rows. You wrap it into the Table Filter macro to leave only required countries - the output 1 is a table with 70 rows. Then you wrap the Table Filter macro into the Pivot Table macro and aggreate your data - the Pivot Table will see only 70-row table, not the original 100-row table. The output 2 will be a 3 row table as for your case.
Then you use the Table Transformer macro - it works with this 3 row table, adds numbers to your rows and sort them - it's the output 3.
The output 4 will be to use another Table Filter and hide the additional column with numbers created on the previous step.
By the way, one of a wide spread use cases for the Table Transformer on top of the Pivot table macro is to rename the automatically generated columns with long names such as "Sum of something".
And what for do you use your first Table Transformer? That is wrapped in the Pivot Table macro?
With such multi-dimensional pivot the format will be ruined - the Table Transformer may combine the merged cells in the headers and you'll get smth like 'Literal of OTA Markets US'.
And you'll need another query to rename your colums and define their sequence - my previous answer covers the rows sorting.
Can you please wrap the Pivot Table into the Table Transformer macro without any SQL queries and make me a screenshot of your modified header row? I'll recreate the table and provide you with a new SQL query.
The above is after I took the Pivot table and wrapped it into a Table Transformer and renamed the columns. So, thank you! this works...
But the bigger concern here is ,
1. When new markets get added, this Wiki page has to be manually updated for those new Markets. In my previous design, with only the Pivot table output no additional manual intervention is needed once you publish the wiki no matter how many markets get added in the future.
2. What does "preserve the original formatting" in the Pivot table really do? I thought that was to honor the sorting i had with table transformer that is the source to this Pivot table.
thanks hugely for all your help!!!!!
You mean that the "Preserve default sorting" option for the Pivot Table doesn't work for this case? It ssems that's because your table is a complex one, we have a remark that "Default sorting is available only in the pivot tables with one column selected as row labels".
What about my question #1 ? Do you agree with my thoughts there? any ideas from your side to get out of this manual intervention?
Also, the output I attached is what you were suggesting correct? when you said you will give me a SQL query? Just validating that I got this solution correct, that's all...
I've thought that you did everything right as you attached a screenshot with the shortly renamed colums.)
Here is a similar question where it is shown that you can list your columns in a certain order via the Table Transformer and they will be shown in that precise order.
What concerns the new markets and manual correction - nothing more from my side, unfortunately.
Hi Atlassian Community, My name is DJ Chung, and I’m a Product Manager on the Confluence Cloud team. Today, I’m excited to share a new and improved version of Home. The new Home helps you ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events