Question

Comparing yesterday to the last n same days

  • 27 June 2018
  • 4 replies
  • 999 views

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

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.

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

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

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