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
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.
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
Hi @tatuspark,
A Native derived table is defined in LookML, while a SQL derived table is defined in SQL. I would say it generally depends on your preference. You use an existing explore as the base for an NDT. With a SQL derived table you use a SQL query to create the derived table.
This article talks about some of the features of both options: https://docs.looker.com/data-modeling/learning-lookml/derived-tables#simple_example
Thanks,
Eric