Question

Values on the week 1st day

  • 12 October 2021
  • 2 replies
  • 17 views

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


2 replies

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} ;;

Userlevel 6
Badge +1

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

Reply