Question

Current 4 Weeks, 4 Prior Weeks, 52 Weeks Prior

  • 22 March 2017
  • 3 replies
  • 218 views

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?

Reply