Search code examples
looker-studio

Custom sorting table in Google data studio based on table row

Hoping someone can help!

I am trying to custom sort a table in data studio.

Here is the code I am using:

CASE
  WHEN REGEXP_MATCH(Event Label, "(?i)Search cars - button click") THEN 9  
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - search performed (step 1)") THEN 8 
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - car details page (step 2)") THEN 7 
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - instant book - button click (step 3)") THEN 6 
  WHEN REGEXP_MATCH(Event Label, "(?i)Returning Renter - Login (step 4)")THEN 5  
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - Registration complete (step 4)") THEN 4
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - extras - continue click (step 5)") THEN 3
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - payment complete - deposit paid (step 6)") THEN 2  
  WHEN REGEXP_MATCH(Event Label, "(?i)Renter - payment complete - booking confirmed (step 7)") THEN 1  
  ELSE 0
END

I would like '9' to be the first row, '8' to be the second below it, etc.

However, when I apply, the table is not reflecting this order. Is my code wrong? I am not getting an error when running it.

I am using the filter in the 'Sort' field as per the screenshot below.

enter image description here

Solution

  • Notice your custom field is being treated as a metric:

    Sort field

    How to notice: Field is blue, and the aggregation function is displayed in the left (=SUM).

    This way, the individual numbers of this are being summarized before sorting. In short, to solve your problem, you need to make this field be green (so it is treated as a dimension).

    Since I don't have access to your dashboard, I listed some options you may try to follow:

    1. Try to select the field again to check if it appear as green as an option. If so, just select it and you're done;
    2. If you created the custom field as a metric instead of a field, try to delete it and create again using the proper type (there's no way to change type, as far as I can tell). Then, go to (1);
    3. If none of these options work for you, try to add the field as a dimension in the table. This way it will be displayed in the table (I don't know if this bothers you), but this way it doesn't matter if the sort field is a field or metric (green or blue) because it will be treated as a dimension in the table.