How to implement DatesYTD and DatesMTD using LookML

[salehaxsid]

Implementation of DatesYTD, DatesMTD using LookML is simple, you just have to create another explore with sql_where condition of existing view that has transaction date. 
For Example:

We have a view "Orders" and it has transaction dates as Order_Date and there is "orderdetails" view which is the fact table.
The Model file will have something like:
 explore: orders{ }

explore:orderdetails{
join : order{

type: lef t outer

sql_on: orderdetails.orderid=orders.orderid

relationship: many to one

}}

Add a date filter in fact table orderdetails.view:
filter: DateFilter{

type: date

}
Now we will add DatesYTD explore in Model file:

explore: DatesYtd{

view_name:orders

}
Join this DatesYTD with Order details:
explore: Orderdetails{

join: datesytd{
     type: left_outer
     sql_on: ${orderdetails.order_id}= ${datesytd.orderid};; 

--[OrderID is primary key and orderdetails have expansion of order view]

     relationship: many_to_one
     sql_where: ${datesytd.orderdate}>=cast (Date_trunc({% date_start datefilter %}, year) as timestamp) and
    ${datesytd.orderdate}<=cast(LAST_DAY(cast (Date_trunc({% date_start datefilter %}, month) as date))as timestamp) ;;

--[logic to derive the value from DateFilter using liquid variable reference and compare it with OrderDate to filter Data according to YTD format]
}}

Now apply the Sql_where condition on orders explore where it is being joined by orderdetails as well so that the transaction dates present in orders get filtered by the date filter we created in orderdetail:

sql_where: {% condition datefilter %} ${orders.orderdate} {% endcondition %}

So now you can use the Date column present in  DatesYTD explore to filter out data according to YTD and also to filter the transaction dates in Orders view.

In dashboard we will be using the filter we created in orderdetails view. Whenever user selects a date in DateFilter, it is being used by DatesYTD explore and Orders explore to filter dates in a way that selected month date is made as last date of that month and first date will be 1st Jan of the year selected in filter.

With this method you can use normal dates for normal filtering of Data and DatesYTD for YTD format filtering too, also you can implement the same workaround for DatesMTD by changing the condition in sql_where clause.

[Amanda-Google · Accepted solution]

Thanks so much for posting this walkthrough! Very helpful for users with a similar use case. Time periods can be difficult to use and you laid it out in an easy to follow manner. 

[salehaxsid]

You're Welcome!!

Also, I figured out to do the same YTD, MTD in the same explore and it will be easier to work since dashboard filters will be applicable on the respective explore along with cross filtering. 
User can implement the same logic in previous post and join YTD, MTD explore with the fact table along with orders table without having to create a different explore having separate join with fact table to make it work like YTD and MTD.

[abhi_augin]

Hello, please can you share the solution do this in the same explore

[GavinW]

You could create a measure in the explore that is something like this - the SQL code here is for BigQuery, so you would need to tailor it to your own DB if it is something else:

 measure: ytd_sales {
    type: sum
    value_format_name: usd
    sql: CASE WHEN DATE(${created_date}) BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND CURRENT_DATE()
    THEN  ${product_retail_price}
    ELSE NULL
    END
    ;;
}

What we are basically doing is telling Looker to include the value of the row in the metric if it matches the condition in the case statement, or ignore it if it does not. And the condition is a check if the row belongs to this year. You can do the same logic for a month to date. LLMs like Gemini are very handy for creating the SQL code for you if you tell it the right dialect of SQL to use.