Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Looker Calculated Fields

  • 27 April 2016
  • 2 replies
  • 164 views
C

Hello,


I want to create a table that lists out the average clicks per day based on the filters for weekday (5 days) vs weekend (2 days).


If I set the filter for “past 14 days”

I would like to see:

Weekday | clicks (sum of Mon-Fri for last 2 weeks) | clicks (daily average of Mon-Fri for last 2 weeks)

Weekend | clicks (sum of Sat-Sun for last 2 weeks) | clicks (daily average of Sat-Sun for last 2 weeks)


I’ve tried using calculations but I can’t seem to get it to divide by the right number. I’ve also tried doing a LookML code but am getting stuck.


Because the count of how many weekdays or weekends depend on the filter. If I select last 7 days, then it is counted only once. If I select last 10 days, then I could get 2 Wednesdays, but only 1 Saturday.


Any help would be appreciated.

Thanks.

2 replies

M
Userlevel 2

I think the following should work.


- dimension: is_a_weekday

  type: yesno

  sql: weekday(column)<5 -- or similar, depending on your SQL dialect

- measure: count_of_days

  type: number

  sql: count(distinct date(column)) -- or similar, depending on your SQL dialect

and then



  • Filter: last 14 days

  • Dimension: Is A Weekday

  • Measure: Clicks

  • Measure: Count Of Days

  • Calculated Measure: Clicks / Count Of Days

C

Awesome, thanks a bunch!!

Reply


Terms & Conditions