nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Accepted answers

Katerina Kovriga _Stiltsoft_

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.

All comments

Katerina Kovriga _Stiltsoft_

Hi @Dolly Kirubavathi ,

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.

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?