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

[Dolly Kirubavathi]

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!

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

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:

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

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:

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.

[Dolly Kirubavathi]

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?

[Katerina Kovriga _Stiltsoft_]

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

[Dolly Kirubavathi]

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?

[Katerina Kovriga _Stiltsoft_]

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.

[Dolly Kirubavathi]

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

[Katerina Kovriga _Stiltsoft_]

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.

[Katerina Kovriga _Stiltsoft_]

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?

[Dolly Kirubavathi]

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

[Katerina Kovriga _Stiltsoft_]

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.

[Dolly Kirubavathi]

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

[Katerina Kovriga _Stiltsoft_]

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

[Dolly Kirubavathi]

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

[Katerina Kovriga _Stiltsoft_]

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.

[Dolly Kirubavathi]

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