Filter order on multiple filters

[Kevin-v3l]

Hello everyone!
I am new to looker and want to know how I can change my "filter order" in the dashboard.

I have something like the list below. I want to dynamically filter on customer from a dropdown and then get all the records with the latest timestamp. 
ex. Customer = Google => All records from google with 25/08/2023 - 10:00 as value.
ex. Customer = Microsoft => All records from microsoft with 20/08/2023 - 18:25 as value.

I have a dropdown filter for the Customer and I can filter on the last date via a yes/no button. But when I turn on my last date filter (y/n) then data won't load if my selected customer does not have the latest date. So for example, I select Apple as value, it does not have a timestamp with the last date (because a few google entries have it) so it simply won't show any data at all.

LookML code for latest timestamp filter:

Does anyone know how I can change the order so it will firstly filter on the customer and then on the latest date of the customer?

Oh and btw just selecting which filters need to update does not work. I can not update timestamp by customer. I can do it the otherway around from latest timestamp to customer but that is the wrong way around. Because then it first filters on Latest timestamp and then gets the customer with the last timestamp.

Thanks in advance!
Kevin

[a_shah]

Hi @Kevin-v3l 

I have come across similar requirements and RANK function has been always helpful in resolving it. add a rank that is partition over customer and ordered by timestamp and then filter for rank '1'.  However, RANK can take into account millisecond and can give unexpected results. You can use RANK filter in a subquery with timestamp truncated to minutes or seconds based on what you call latest data in your dataset.

Hope this helps.

~Ashish

[Kevin-v3l]

Hello @a_shah 

Thank you so much! This helps a lot of setting me on the right track. I have got a sql query working like it should. So it gives the rank 1 to every last timestamp for each customer and the rank 2 for the same customer but with an older timestamp. If I can filter on this, it should work! I ended up, fixing it another way in the end tho.

SQL:

SELECT customer, timestamp,
DENSE_RANK() OVER(PARTITION BY customer ORDER BY timestamp DESC) as Rank
FROM table

Returns:

Final solution (by using a new view and derived table):

I could not get it to work in a dimension and kept getting sql errors if I tried. I ended up fixing it a different way by creating a derived table and an extra view for customer/timestamp. So one view with a select of all my columns. And one view with a select on customer and MAX(timestamp), grouped to customer. (So I get the max times grouped per customer, then I just need to filter on customer). Then I joined them in my model with as primary key customer & timestamp.

Now in my report I make sure to use the customer and timestamp field from the second view. I hid the original customer from the first view by adding "hidden: yes" to it so I can only use the correct one.

Original mytable view:

#Select all from table
sql_table_name: default.mytable  

#Create primary key dimension
dimension: primary_key {
    hidden: yes
    type: string
    primary_key: yes
    sql: CONCAT(${customer},${time_stamp_time}) ;;
  }

New mytable_timestamp (and customer) view:

#Create derived table in new view
view: mytable_timestamp {
    derived_table:{
      sql: SELECT Customer, MAX(TimeStamp) as MaxTimestamp
                  FROM mytable
                  GROUP BY  Customer ;;
    }

    suggestions: yes

#Primary key for later joining on
    dimension: primary_key {
      hidden: yes
      type: string
      primary_key: yes
      sql: CONCAT(${customer},${time_stamp_time}) ;;
    }

#Customer dimension
    dimension: customer {
      type: string
      suggestable:  yes
      sql: ${TABLE}.Customer ;;
    }

#MaxTimestamp dimension
    dimension_group: time_stamp {
      type: time
      timeframes: [raw, time, date, week, month, quarter, year]
      sql: ${TABLE}.MaxTimestamp ;;
    }
  }

Mytable.Model:

---

---

#Join tables in model file on primary key
explore: mytable {
  join: mytable_timestamp {
    type: inner
    relationship: many_to_one
    sql_on: ${mytable.primary_key}=${mytable_timestamp.primary_key} ;;
  }