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}))