Current 4 Weeks, 4 Prior Weeks, 52 Weeks Prior

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

1 Like

Like the idea. Can’t seem to replicate on Redshift. What would I need to adapt?

Hello - I’m not experienced w/ the redshift dialect. Maybe someone on this forum can take a look? I can say it’s worth it if you need this type of reporting as we now use this methodology for a variety of KPIs. Sorry! Steve

1 Like

What is the logic behind 25 days in here and for 10 weeks rolling average should i replace 28 with 70?