Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Comparing yesterday to the last n same days

  • 27 June 2018
  • 4 replies
  • 999 views
G

Hi everybody,


I’m new to Looker, and I’m trying to understand if I can create a simple table to compare yesterday (it was a Wednesday) to the average of the previous 4 or 6 Wednesdays of this year.



I’ll try to explain my request better:


we receive daily a certain number of leads, but usually they’re not equally distributed during the week, so for instance usually on Monday and Tuesday there are more leads, while on Wednesday it’s less, and so on.


So I’d like to have a table with 2 columns: yesterday, and the last n days that are the same day of the week to understand how yesterday went, so I can immediately spot if it’s a normal trend or if it’something that is worth more analysis.



Thanks a lot


Giovanni

4 replies

JohnRomanski
Userlevel 1

Hi there, I don’t have an answer for this, but really the same question. I wanted to filter an entire dashboard based on the current day of week.



So the week over week Looks, (that I have achieved by filtering on Report Date -> “Day of Week”= “Monday” ) will automatically filter on Report Date -> “Day of Week”= Day of Week (today).



I am just not sure at what level to insert this logic.

JohnRomanski
Userlevel 1

Looks like you can solve it using a yes/no filter in the view. read the discourse here. Filtering on the same day of week as today

P
Rank
Userlevel 1

Hi @JohnRomanski!



That is exactly correct! This will work for your example and the example @Giovanni_Pravadelli has provided as well. We can create a yesno dimension to filter on that will only return the same day of the week for every week. The discourse article you have provided gives some great examples on how to achieve this. The way I have done this before is to just add the time frame day_of_week to my dimension group time frame. I then create the yesno dimension for filter like this:



 dimension: cur_day {


    type: yesno


    sql: ${date_day_of_week} = dayname(curdate()) ;;


  }


This is in MySQL and will just need to be tailored to your flavor of SQL. We can then filter on the yesno field and perform your week-over-week analysis. Let us know if you have any other questions!



Best,


Peter

G

Sorry, but I didn’t see the reply…


Thanks a lot for the support,


Now I’ll try to implement it



Thanks again


Giovanni

Reply


Terms of Service