Rank

tatuspark · 12-03-2020 05:29 AM

Hi,

I would like to calculate the rank in LookMl (i don't want to use table calculation) of my dimension according to the different measures as in the table below.

Thank you for your answers

DickyMohanty

Hi,

Please refer the Point/Section 3 - ‘Top N Items Versus the Rest of the Population’ in this URL to implement the Top N/Rank according to your requirement.

https://help.looker.com/hc/en-us/articles/360023573273-Great-Use-Cases-for-Parameter-Fields

Also share how did you tweaked according to your example.

Eric_Lyons

Hi @tatuspark,

Another common way to do this, is to use a SQL derived table or a native derived table to add a window function. This way we can use a rank and partition by function and create a dimension for rank. I have listed an example below for both a native derived table a SQL derived table. The SQL syntax used in this example is Snowflake.

####NDT Version:
view: NDT {
  derived_table: {
    explore_source: order_items {
      column: user_id {}
      column: delivered_date {}
      derived_column: test {
        sql: rank() over (partition by user_id order by delivered_date);;
      }

    }
  }
  dimension: user_id {
    type: number
  }
  dimension: delivered_date {
    type: date
  }
  dimension: test {
    type: number

  }

####SQL Derived Table Version
SELECT
    order_items."USER_ID"  AS "order_items.user_id",
    TO_CHAR(TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST(order_items."DELIVERED_AT"  AS TIMESTAMP_NTZ))), 'YYYY-MM-DD') AS "order_items.delivered_date",
    rank() over (partition by "order_items.user_id" order by "order_items.delivered_date") as rank
FROM "PUBLIC"."ORDER_ITEMS"
     AS order_items


GROUP BY 1,TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', CAST(order_items."DELIVERED_AT"  AS TIMESTAMP_NTZ)))
ORDER BY 1 DESC
LIMIT 500

Please let me know if you have any questions!

Thanks,

Eric

tatuspark

@Eric_Lyons Thanks to your answer.

What is the difference between Native Derived Table and SQL Derived Table ?
In which case do we use one or the other?

Thanks

Eric_Lyons