Sorting Issue: Combination of Text and Numeric Format leads to incorrect sorting

[CRS]

Seeking for your assistance on this:

Background:

Initially, the "Rank" column in our dataset is set to numeric and the rankings outside top 100 is displayed as "0".

We use this formula to address the issue:

CASE
WHEN rank = 0 THEN "Not in top 100"
ELSE CAST(rank AS STRING)
END

When we use the formula, the calculated field is now formatted as text. But this results in incorrect sorting. For example, rankings appear as 1, 10, 11, 2, 21, 22, with "Not in top 100" appearing at the top. To resolve the sorting issue, we’ve created a calculated field with a numeric data type to ensure proper sorting, which moves "0" values on the "Rank" field to the end of the table:

This is the formula we use:
CASE
WHEN Rank = 0 THEN 1000
ELSE Rank
END

Challenges:

Although the new calculated field provides accurate sorting, we need to retain the original "Rank" column to ensure sorting remains valid. Removing this column disrupt the sorting process, as it relies on the presence of the original "Rank" column.

Could you please advise on how we can maintain the correct display of "Not in top 100" while ensuring accurate sorting without relying on the original "Rank" column?

Thank you for your assistance.

[marcwo]

Hi,

do you still need to show the values that are outside of the top 100?

If not, I would create a yesno dimension to determine whether something is top 100 or not, filter the false out and that way, you could still the original rank column for sorting.

dimension: is_top_100 {
type: yesno
sql: rank != 0
}

Best
Marc

[CRS]

Hi Marc,

Yes, we want to see the values outside top 100 and it should be displayed as "Not in top 100" in the table with the correct sorting.

[CRS]

Also just an FYI, we are using the keyword.com tool as our Data source in Looker studio report. Thanks in advance for your help!

[lauratilton]

If you want to display numbers as text, and still have them sort correctly, you could do something like this. You could also concat spaces instead of zeroes and that would work great as well.

CASE 
WHEN Number>0 and Number<10 then CONCAT("00",Number)
WHEN Number>10 and Number<100 then CONCAT("0",Number)
WHEN Number=100 then "100"
WHEN Number>100 then "Not in Top 100"
END

[lauratilton]

Sorry, there was an error in what I posted initially...I fixed it but will also put it in this reply. 

CASE 
WHEN Number>0 and Number<10 then CONCAT("00",Number)
WHEN Number>10 and Number<100 then CONCAT("0",Number)
WHEN Number=100 then "100"
WHEN Number>100 then "Not in Top 100"
END

[CRS]

Hi Laura, we tried the formula you advised, but we're still getting an incorrect sorting. Please see image below for reference.

Note: We are using keyword.com tool as data in looker studio report.

[lauratilton]

You used the formula I suggested for Current Rank as well as Initial Rank? Did you use spaces instead of zeroes, making sure to use two spaces for 0-9 and one space for 10-99? I think you may have used the formula I listed initially which was incorrect (I corrected it online instantly and sent a followup message, but maybe you didn't see it?)