Issue with Custom Sorting in Looker Studio Pivot Table

[Shwetakulka]

Description: I'm trying to create a custom sorting order for a pivot table in Looker Studio. My pivot table includes semester data with values such as "Fall 2024", "Summer 2024", "Spring 2024", and "Fall 2023, Summer 2023, spring 2023 and so on". I need these values to appear in a specific order: "Fall 2024" first, followed by "Summer 2024", then "Spring 2024", and so on.

Steps Taken:

1. I created a calculated field named Custom Semester Order in my data source using queries. 

2. I added this Custom Semester Order the field to column section to the pivot table.

3. I used the Custom Semester Order field for sorting in ascending order.

Problem: I want to use the Custom Semester Order field only for sorting purposes and not display it in the pivot table. However, I’m not sure how to hide this field from being displayed while still using it for sorting.

Question: How can I configure my pivot table in Looker Studio to use the Custom Semester Order field for sorting without displaying it in the table itself? or how can i sort my values. 

[lauratilton]

Instead of trying to put your custom field directly into the Sort, click Add Sort and then create a chart-level calculated field that just consists of the name of your custom field. You can add it there even if you can't add it directly into the Sort.

Another trick: Values starting with spaces will sort based on the number of spaces BUT the spaces won't show. Example: a calculated field called "Order" resulting in "First", " Second", " Third", and " Fourth" will sort accordingly. Not sure if that would work here, since you don't have a set number of values, but it's another option.

[misslooker]

I'm trying to do this as well and I don't see an option to "click Add Sort" and create a chart-level calculated field.  I the chart setup properties, I see a sorting section, I see Row #1 and there is the count of the row, which makes no sense.  If I click on that, I only see the metrics being used in the pivot.  There is no option to choose a field in the dataset that is based on a case statement to create the custom sort.  I also tried creating a calculated field, but I can't choose it because it's not part of the pivot.  

It's so odd that this isn't easier to do. 

[aapomanni]

Have you been able to fix this? @Carlos_Leon reply has worked in the past but now I have the exact same issue as you @Shwetakulka that you can't add sort fields to the sort selection and instead you only have the field itself and the metric but I'd need to use a separate sort column.

[aapomanni]

I was able to fix this now using @lauratilton trick with space in front of the string value. I am using Custom SQL on top of BigQuery and have a sort column with numeric values so it was fairly easy to fix using that. I added a column with the following SQL. 

CONCAT(REPEAT(" ", sort), name) as name_with_whitespace_sort

 Unfortunately it seems that REPEAT function is not available when using calculated fields so then you need another workaround

[prashant_sn]

@Shwetakulka  did you got the solution for,

Question: How can I configure my pivot table in Looker Studio to use the Custom Semester Order field for sorting without displaying it in the table itself? or how can i sort my values.