Solved

How to calculate Sales Date dimension

  • 12 December 2021
  • 22 replies
  • 355 views

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  

  1. Sales of latest year and latest month(Dec 2021)
  2. Sales of latest year and this month(Nov 2021)
  3. Sales of last year and this month(Dec 2020)
icon

Best answer by Dawid 16 December 2021, 21:42

View original

This topic has been closed for comments

22 replies

Userlevel 7
Badge +1

And what did you try so far?  For calculations like these, you could use advanced filter functionality in Looker.

 

For current month:

filters: [transaction_date: “this month”]

 

For previous month:

filters: [transaction_date: “1 month ago”]

 

For the same month as current but last year:

filters: [transaction_date: “12 months ago”]

 

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. 

I am trying to create this kind of chart:

 

Userlevel 7
Badge +1

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: [transaction_date: "1 month ago"]
  }

  measure: This_Month{
    type: sum
    sql: ${TABLE}.total_sales;;
    filters: [transaction_date: "this month"]
  }

  measure: This_Month_Last_Year{
    type: sum
    sql: if(EXTRACT(DAY FROM ${transaction_raw}) <= EXTRACT(DAY FROM CURRENT_TIMESTAMP()), ${TABLE}.total_sales,0) ;;
    filters: [transaction_date: "12 month ago"]
  }

 

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?

Userlevel 7
Badge +1

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

 

Userlevel 7
Badge +1

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.

Userlevel 7
Badge +1

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?

Userlevel 7
Badge +1

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?

Userlevel 7
Badge +1

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.

Userlevel 7
Badge +1

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?

 

Userlevel 7
Badge +1

Yes, that should work :)

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

and what if my week starts from Wednesday? does ISOWEEK  works with Wednesday as week start day.

Userlevel 7
Badge +1

No it won’t. ISOWEEK is an ISO  standard implementation when it comes to days of the week. Starting from Monday  as 1 and ending with Sunday as 7.

 

If you want to “play with time” and create customised weeks then you would have to translate timeframes selected in Lokker, for example “Last 1 completed Week” to not be a simple calculation of difference of weeks, you would have to shift the days . It’s possible but also hacky - I needed to do it once when a reporting week (for one explore) was Friday - Thursday… 

In short - it’s a nightmare :D