Hi,
I am trying to create a measure which only brings in value for the first day of the week.
I tried to create using filtered measures but couldnt make it work.
For ex, take the value of GOOG stock. it varies every day. Now I want a measure which would return only the value on monday or tuesday(if monday is a holiday).
lets say my filter is set and for the last 2 days and stock name as GOOG it should show as below
Stock Name Date Value valueonweek1st day
GOOG 10/12/2021 $1000 $995
GOOG 10/11/2021 $995 $995
The right most column has only values for the week1st day, is there a way to achieve this with lookml
If you have week number and day of the week in your data, then you could probably use SQL to get this value, the holiday part would be more difficult as you need a reference point if a particular Monday is a holiday, which means it needs to be either part of the dataset or a join to a holiday dataset
For now I am trying to use the date_trunc function and first_value windown function to bring in the values
WHile I was sucessful to bring in the values for the week first day when i use a range in the date filter,but if i filter for a particular day it doesnt work again. it shows me the same value as the day instead of the week first day
this is how i defined my measure
measure: FIRST_VALUE(${measure1}) over partition by DATE_TRUNC(‘week’,${Date.date}::Date) order by ${Date.date} ;;