Get the latest date of whole dataset

Hello,
I have a date column and I want to get the latest date of this column. This table is updating every week, so last date is changing continuously. I cannot define a static date as last_date. I saw a table calculation solution in community but I desperately need a formula to use in view. I will use this last_date dimension in another dimension. Any suggestion (other than max(date))?
Thanks in advance.
 

de4ed178-b425-4b04-9aa3-2495a251be15.jpg
0 6 1,686
6 REPLIES 6

IanT
Participant V

You will need to create a derived table to select max date over whole dataset rather than grouping by sales date (your example). You can do this via NDT to bind filters (say you want max date with sales in a specific region its not always going to be the same as the max date of the whole dataset).

Another option is creating a sql window function but I dont recommend doing this in looker as you have to be careful what else you do in your model.

Thanks @IanT 
I created an NDT and received this error: Cannot specify both sql_table_name and derived_table for view..

Then I created a new view and defined last_date in this view. I included this view in my main view and defined a new measure in this main view. Unfortunately it did not work. Last_date seems to be grouped weekly.  Why?
I receive this warning in my explore Missing values for 'table.last_date' were not filled. It is unclear what values to fill for this field.

IanT
Participant V

remove your ndt view back to how it was before you made any changes.

In your expore just select the measure which is max date...no dimensions...you should get a single field for the 1 date.

Goto the cog and get lookml (derived table) and then copy paste that code into a new file.

Join that view file into your existing model using a one_to_one relationship with sql_on: 1=1 (or join is using a cross join.

This will give you that single date across every row of your data. If you want to have different dates for different segments of your data (eg. sales region) then you will need to think carefully about how you join the max dates into your data as you will need to change your ndt to include those dimensions.

The problem is I don’t want to gat last_date as a measure. If I were ok with measure, I would easily use max(${transaction_date}) and get the max date.
measure: last_date {
    type: date
    sql:max(${date});;
  }


I have to create a last_date dimension in order to use it in another dimension. Stg like
dimension: final_stock{
    type: yesno
    sql: case when ${date} = ${last_date} then ${stock} else 0;;
  }

So I need help on creating a dimension

IanT
Participant V

you declare it as a dimension on the ndt view

ok, that is great.
What is cog you mentioned above?
Goto the cog and get lookml (derived table) and then copy paste that code into a new file.

Additionally, you said that, This will give you that single date across every row of your data. 

That is what I want

Top Labels in this Space
Top Solution Authors