I have one date dimension_group “transaction_date”(Year, month, month num…) and one column sales. I need to create three measures or columns which works dynamically
And what did you try so far? For calculations like these, you could use advanced filter functionality in Looker.
For current month:
filters:
For previous month:
filters:
For the same month as current but last year:
filters:
Thanks Dawid for your response.
I am trying to achieve YTD measures.
Lets say today is 13 Dec
So :
I want to understand how to create these measure either in scripting or Table calculation.
I am trying to create this kind of chart:
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.
Dawid,
I have created three measures as:
measure: Previous_Month{
type: sum
sql: if(EXTRACT(DAY FROM ${transaction_raw}) <= EXTRACT(DAY FROM CURRENT_TIMESTAMP()), ${TABLE}.total_sales,0) ;;
filters:
}
measure: This_Month{
type: sum
sql: ${TABLE}.total_sales;;
filters:
}
measure: This_Month_Last_Year{
type: sum
sql: if(EXTRACT(DAY FROM ${transaction_raw}) <= EXTRACT(DAY FROM CURRENT_TIMESTAMP()), ${TABLE}.total_sales,0) ;;
filters:
}
Can you explain me in detail how it works?
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
Your solution sounds promising
Second, if you see my measures, I am using <= EXTRACT(DAY FROM CURRENT_TIMESTAMP()) to restrict data up to current Day number of month. Is it any way to restrict data up to First day of week like Monday of current week which is today, so data will come to Explore up to Sunday?
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.
Dawid,
You code went from top of my head.
Let me ask you my question in steps:
Suppose my week starts from Monday. I need to show data up to last Sunday. My database dialact is Bigquery.
Today is 17 Dec. chart should have data up to 12 Dec. No matter it is MTD or WTD.
as per algorithm I should do, Subtract 7 days from CURRENT_TIMESTAMP(), then find the week end date and filter the date dimension upto last date of last week.
How can I do it in Looker?
Thanks,
Rohit
In this case you could just use Looker built-in filters and select Last 1 Completed Week
I think “Last 1 Completed Week” gives data only for 7 days. My table has 2 years data. I need latest data up to last Sunday because this week starts from Monday.
Lets take it this way, I want to restrict the whole data present in project to Last Sunday.
So you want to exclude the current week. Then if I were to use my calendar table join I would make sure I filter week_offset < 0
I actually did something similar but on Months, I allowed people to select a Month and then using offsets compared it with previous, last year’s, and the 8 ones preceding the previous:
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?
What you need is a dynamic point of reference between your data and now. You could create a following dimension:
dimension: week_offset {
type: number
sql: DATEDIFF(WEEK, CURRENT_DATE(), ${TABLE}.date) ;;
}
and this would basically be your week_offset. Depending on your dialect. I use it so that negative values tell me it’s in the past and positive in the future. 0 means current week
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?
It’s hard to understand fully what you’re doing there as I can only see different parts of what you’re doing. You must have some kind of filter already if it only shows you one week of data? But you just said you had 2 years of data. If you have two years but don’t want to show current week then the week offset will work
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.
What dialect you’re using. Try to use ISOWEEK in DATE_DIFF
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, that should work 