1

enter image description hereI am trying to shift the start date of the transactional (don't know if thats a word) week from sunday (index 0) to friday (index 6). This is the only view in any of our data models that would use this transnational week. I am creating a 2 new dimensions in the specific view this report is generated from. It is for a week over week report so there would have to be a transaction week associated with it, but even though the week would start friday, the transaction week would have to be read as the next week. (example start Friday 10/25, but transaction week is still read as 10/28).

I have tried to create two new dimensions and even had multiple extensive conversations with looker support and we couldn't come to a feasible solution. 

[![dimension: friday_start {
    type: number
    sql: case when ${transaction_datetime_day_of_week} = 'Friday' then 1
      when ${transaction_datetime_day_of_week} = 'Saturday' then 2
      when ${transaction_datetime_day_of_week} = 'Sunday' then 3
      when ${transaction_datetime_day_of_week} = 'Monday' then 4
      when ${transaction_datetime_day_of_week} = 'Tuesday' then 5
      when ${transaction_datetime_day_of_week} = 'Wednesday' then 6
      when ${transaction_datetime_day_of_week} = 'Thursday' then 7
      else null end;;
  }
  dimension: friday_week_start  {
    sql: ${transaction_datetime_raw};;
    type: date_day_of_week
    order_by_field: friday_start
    convert_tz: no
  }][1]][1]

The dimension works, but it aggregates the total sum of transactions for friday-thursdays, which could be useful in future predictions and modeling, but that's not the point.

samdaily34
  • 67
  • 7
  • 1
    So you need group dates from 10/25 till 10/31 under one week marked as 10/28, and also 10/18 till 10/24 under other week marked as 10/21. Do I understand correctly? If so, then you can write it directly in SQL: DATE_TRUNC('week', ${transaction_datetime_raw}+interval '3 days')::date – Edgars T. Oct 30 '19 at 13:45

1 Answers1

1

Your report comes from an explore, and that explore is in a model. Each model can have its own week_start_day property (https://docs.looker.com/reference/model-params/week_start_day). If more than one query from this explore should treat the start day of the week as Friday, then this would be my approach.

  1. Create a new model
  2. Add this property to it... week_start_day: friday
  3. Find the explore which is behind the report by clicking the "explore from here" button and then click the Looker logo in the area just above the field picker to be taken to the LookML which defines that explore
  4. Copy or Cut the explore from that location and paste it in your new model
  5. Modify any existing saved reports by using the "Find and Replace in all content" button in the Content Validator if you fully moved the explore, or find only the desired content to be moved and do the smaller replace on them one by one. If you need to move a lot, but not all, there are tricks on how to fool the content validator into helping you do that.

~A Looker

Andy Crutchfield
  • 11
  • 2