Looker Date Calculations
In the formulas below, pay special attention to where now() is referenced. Most formulas below will assume yesterday is the latest date. Sometimes in your formulas you might want to go back an extra day which means you need to update the add_days calculation.
Last 30 Days
Formula: ${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < trunc_days(now()) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_days(-30, trunc_days(now()))
Note: Purposefully Omits “Today” since there’s very little chance there will be any actual data
30 To 60 Days
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < add_days(-30, trunc_days(now())) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_days(-60, trunc_days(now()))
Note: Purposefully Omits “Today” since there’s very little chance there will be any actual data
Last 3 Months
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < trunc_months(now()) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_months(-3, trunc_months(now()))
Note: Purposefully Omits “Today” since there’s very little chance there will be any actual data
3 To 6 Months
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < add_months(-3, trunc_months(now())) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_months(-6, trunc_months(now()))
Note: Purposefully Omits “Today” since there is very little chance there will be any actual data.
MTD
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= trunc_months(add_days(-2, now())) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < trunc_days(add_days(-1, now()))
Note:Assumes Yesterday Is The Last Day With Data (and represents the current month)
Last Month
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_months(-1, trunc_months(add_days(-1, now()))) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < trunc_months(add_days(-1, now()))
Note:Assumes Yesterday Is The Last Day With Data (and represents the current month)
Two Month Ago
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} >= add_months(-2, trunc_months(add_days(-1, now()))) AND
${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date} < add_months(-1, trunc_months(now()))
Note: Assumes Yesterday Is The Last Day With Data
Last Week
diff_days(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_week}, add_days(-1 * mod(diff_days(date(2008,01,01),trunc_days(now())) + 1, 7), trunc_days(now()))) = 7
Note: Assumes Yesterday Is The Last Day With Data (and represents the current month)
2 Weeks Ago
diff_days(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_week}, add_days(-1 * mod(diff_days(date(2008,01,01),trunc_days(now())) + 1, 7), trunc_days(now()))) = 14
Note: Assumes Yesterday Is The Last Day With Data (and represents the current month)
Age - Months
diff_months(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_month}, trunc_months(add_days(-2, now())))
Note: Make sure you change the -2 to whatever day is the latest day in your analysis
Days In Current Month (Based on now())
extract_days(add_days(-1, add_months(1, trunc_months(add_days(-1, now())))))
Note:Assumes Yesterday Is The Last Day With Data (and represents the current month)
Days In Month (Based on usage month)
extract_days(add_days(-1, add_months(1, ${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_month})))
Days In Month (Based on usage date)
extract_days(add_days(-1, add_months(1, trunc_months(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}))))
Day of the Week
mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)
Note: Assumes Monday is Day 0
Day of Week Written
if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=0,"Monday",if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=1, "Tuesday", if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=2, "Wednesday",if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=3, "Thursday", if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=4, "Friday", if(mod(diff_days(date(2008,01,01),${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}) + 1, 7)=5, "Saturday", "Sunday"))))))
This takes formula above to spell out Date
Current Day
extract_days(add_days(-1, now()))
Note: Assumes Yesterday Is the Last Day With Data (and the current day). PAY SPECIAL ATTENTION to the date it’s giving you and the date your logic wants. For the EoM projection below you actually want add_days(-2, now())
Current Week (using today)
add_days(-1 * mod(diff_days(date(2008,01,01), trunc_days(now())) + 1, 7), trunc_days(now()))
Note: Use “+ 1” for the week starting on Monday and change it to “+ 2” for the week starting on Sunday
Week (using Usage Date)
add_days(-1 * mod(diff_days(date(2008,01,01), trunc_days(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date})) + 1, 7), trunc_days(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}))
Note: Use “+ 1” for the week starting on Monday and change it to “+ 2” for the week starting on Sunday
Usage Date Excl Year in MM-DD
concat(extract_months(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}),"-",extract_days(${cz_usagedate_7c3586378c8f197c69ae2ad6746b8573.d_date}))
Updated 25 days ago