Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Pivot table - is there a way to display the calculated column in a particular order?

Hello,

Use Case:

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

 

Thank you!

 

1 answer

1 accepted

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).

Wrap your Pivot Table macro into the Table Transformer and then into the Table Filter macro:

Mon 2-1.png

Use the Table Transformer macro to assign additional numbers to your items:

Mon 2-2.png

SELECT *,
CASE
WHEN
T1.'Region' LIKE "US"
THEN "1"
WHEN
T1.'Region' LIKE "Can"
THEN "2"
ELSE "3"
END
AS 'Status'
FROM T* ORDER BY 'Status'

And then use the Table Filter to hide this additional 'Status' column if necessary:

Mon 2-3.png

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:

Mon 2-4.png

Hope this helps your case.

Thank you!

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).

I tried the Table filter route as well. In the table filter preview it shows the correct soring but when I wrap it with the Pivot table my sorting goes away?

The Pivot Table macro sorts data alphabetically by default.

So, I suggest using the Table Transformer on the top of the Pivot Table macro to assign a custom sorting order.

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?

Capture.PNG

 

This is my format. Will I be able to preserve this format by wrapping it in Table transformer?

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.

Capture.PNG

 

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.

Thank you for all your help!!!!!! 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

🏠 Say hello to the new Confluence Home!

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 ...

42,639 views 29 133
Read article

Community Events

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

Events near you