How to calculate Sales Date dimension

Thanks Dawid for your response.

I am trying to achieve YTD measures.

Lets say today is 13 Dec

So :

1. Sales of latest year and latest month(Dec 2021) sales up to 13th day
2. Sales of latest year and this month(Nov 2021)sales up to 13th day of last month
3. Sales of last year and this month(Dec 2020) sales up to 13th day of Dec month 2020

I want to understand how to create these measure either in scripting or Table calculation. 

And what have you tried so far? 

For XTD I use an approach that works for more static data like daily but also near or real-time

 I create a view called CURRENT_TIME that gives me the second of day/week/month/quarter/week. Then I create dimensions “before current time of x” that is a result of the current_time and my timestamp comparison.

This is part of my “current_time” or “time_comparisons” view:

view: current_time {

  dimension_group: current {
    type: time
    sql: CURRENT_TIMESTAMP ;;
    timeframes: [date, hour_of_day, minute, day_of_week, day_of_month, day_of_year, week_of_year, day_of_week_index, time_of_day, month, month_name, month_num, time, week, year, raw]
  }

  dimension: second_of_day {
    group_label: "Current"
    type: number
    sql: (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) * 60 * 60) + (EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) * 60) + EXTRACT(SECOND FROM CURRENT_TIMESTAMP) ;;
  }
}

In my data explore I join it like this 

  join: current_time {
    fields: []
    relationship: one_to_one
    sql:  ;;
  }

Then I added a refinement only for one field - my date/timetamp/datetime where I create the boolean fields

view: +my_view{
  dimension_group: timestamp_expanded {
    sql: CAST(${TABLE}.timestampAS TIMESTAMP);;
    type: time
    hidden: yes
    timeframes: [date, week, month, quarter, time_of_day, hour_of_day, time, raw, day_of_week, day_of_week_index, day_of_month, day_of_year]
  }

  dimension: second_of_day {
    hidden: yes
    group_label: "Second of"
    group_item_label: "Day"
    type: number
    sql: ${timestamp_expanded_hour_of_day} * 60 * 60 + 3600;;
  }


  dimension: is_before_now_day { 
    group_label: "Before Current Time of"
    group_item_label: "Day" 
    type: yesno
    sql: ${second_of_day} <= ${current_time.second_of_day} ;;
  }

}

The view above calculates the second of each timeframe for timestamps coming from data. The Current Time gives me a second for each timeframe of this point in time.. Then I just compare. Using this with either filters “last 12 months” or calendar table should give you what you want.
 

In this case you could just use Looker built-in filters and select Last 1 Completed Week

Lets take it this way, I want to restrict the whole data present in project to Last Sunday.

Thanks for the input. Can you please explain in detail how to filter day_offset <  0. Do I need to add this filter on Dimension Group?

Dawid,

I set week_start_day: monday

Then

Then I brought the data in Explore:

You can see, 0 comes from Sunday not Monday. It should come from 13 Dec. I don’t see any negative value.

Yes I have changed the expression you suggested due to database dialect. 

What I am missing?

Yes I want to filter out current week data from all of my charts. Weej Offset could be a solution. I tried to create week Off set but week off set has 0 value start from Sunday despite week_start_day: monday.

Yes ISOWEEK is working fine. 

Last thing. I want to filter current week data from all of my charts. Can I use WeekOfSet as sql_always_where which filter data from entire Explore?

Yes it is working. My last question on this thread is, how exactly ISOWEEK  works ?