Question

# Current 4 Weeks, 4 Prior Weeks, 52 Weeks Prior

• 3 replies
• 306 views

• Member
• 4 replies

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

}

### 3 replies

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

Userlevel 2

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