To create current 4 weeks, 4 prior weeks 52 weeks prior comparison
Establish “Common Date” to make the date current (so 28 days ago becomes current date… and so on)
dimension_group: common_date {
type: time
timeframes: [hour_of_day, time, day_of_week, week, date, month, year]
sql:
CASE
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < 28) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) > -1)) THEN DATE(${TABLE}.created_at)
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < (28 + 28)) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) > 25)) THEN AddDate(DATE(${TABLE}.created_at),28)
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < (28 + (52 * 7))) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) >= (52 * 7))) THEN AddDate(DATE(${TABLE}.created_at),(52 * 7))
ELSE ‘no’
END ;;
}
Establish “common period” Field to define Last28, 4weeksprior, 52weeks prior
dimension: common_period {
type: string
sql:
CASE
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < 28) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) > -1)) THEN ‘Current 28 Days’
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < (28 + 28)) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) > 25)) THEN ‘4 Weeks Prior’
WHEN ((DateDiff(AddDate(Current_Date(),0),DATE(${TABLE}.created_at)) < (28 + (52 * 7))) AND (DateDiff(Current_Date(),DATE(${TABLE}.created_at)) >= (52 * 7))) THEN ‘52 Weeks Prior’
ELSE ‘no’
END ;;
}