Weeks data 4 weeks ago

[#1]

Hello all,

I am trying in earnest to extract just a weeks worth of data from 4 weeks ago. So i want to exclude the last 3 weeks and look at a weeks worth of data from 4 weeks ago. Does this make sense?

I have gone down the variable route with no success so far using the variable below and the then the expression. It just throws back an error.

Is there something glaringly obvious that Im doing wrong? Any other way to do this?

Variable:

vCurrentWeek-3

=Week(Today())-3

Expression:

=sum({$<Metric_Code={'RTT9'}, Activity_Date = {'$(vCurrentWeek-3)'}> Metric_Value)

This is the load script:

LOAD
CCG,
    "fk_calendar"     as Calendar_ID,
    "fk_consultant"     as Consultant_ID,
    "fk_gp"       as GP_ID,
    "fk_metric"      as %Metric_ID,
    1        as Outcome_ID,
    "fk_priority"     as Priority_ID,
    "fk_source_of_referral"   as Source_Of_Ref_ID,
    "fk_Specialty"     as Specialty_ID,
    "Metric_Value",
    "Patient_type",
     "Metric_Part",
    "Activity_Date",
    "weeks_metric",
    "weeks_metric_pivot",
         weekend("Activity_Date") as Weekending,
    if(InYearToDate( "Activity_Date", today(), 0, 4), 'yes', 'no') as YTD_ind,
    if(InYearToDate( "Activity_Date", today(), 0, 4), Metric_Value, 0) as YTD_Activity,
    if(InYearToDate( "Activity_Date", Monthend(today(),-1), -1, 4), 'yes', 'no') as Plan_ind,
    if(InYearToDate( "Activity_Date", Monthend(today(),-1), -1, 4), Metric_Value, 0) as YTD_Plan,
    if(InYearToDate( "Activity_Date", today(), 0, 4), Metric_Value, 0) as Monthly_Activity,
    if(InYearToDate( "Activity_Date", Monthend(today(),-1), -1, 4), Metric_Value, 0) as Monthly_Plan,
    if(inmonth ( "Activity_Date", today(), -2 ), Metric_Value, 0) as PreMon_Activity
   
   
   ;
SQL SELECT
    "fk_calendar",
    "fk_consultant",
    "fk_gp",
    "fk_metric",
    "fk_Specialty",
    "fk_source_of_referral",
    "fk_priority",
    sum("Metric_Value") as Metric_Value,
     case left(CCG,3)
    when '03X' then 'NHS Erewash CCG'
    when '03Y' then 'NHS Hardwick CCG'
    when '04A' then 'High Peak and Buxton CCG'
    when '04J' then 'NHS North Derbyshire CCG'
    when '04R' then 'NHS Southern Derbyshire CCG'
    when '05D' then 'NHS East Staffordshire CCG'
    else 'Other' end as CCG,
    "Patient_type",
    "Activity_Date",
     "Metric_Part",
     "weeks_metric_pivot",
     "weeks_metric"
     
FROM "Team_Planned_Care".dbo."fact_rtt_dash_IP_avg_waiting"

group by
    "fk_calendar",
    "fk_consultant",
    "fk_gp",
    "fk_metric",
     "fk_pct",
    "fk_Specialty",
    "fk_source_of_referral",
     "fk_priority",
     CCG,
    "Patient_type",
     "Activity_Date",
      "Metric_Part",
           "weeks_metric_pivot",
     "weeks_metric"

Any help much appreciated!

Many thanks in advance,

Nick

[Gysbert_Wassenaar]

Activity_Date = {'$(vCurrentWeek-3)'}

I assume Activity_Date is a date field and does not contain week values. If so try

Activity_Date = {'>=$(weekstart(today(),-4))<$(weekstart(today(),-3))'}

[#3]

Many thanks for the quick reply!

So do I add it as an expression like:

=sum({$<Metric_Code={'RTT9'}, Activity_Date  = {'>=$(weekstart(today(),-4))<$(weekstart(today(),-3))'}>} Metric_Value)

or do i need to create a variable as well?

Apologies, my brain is frazzled at the moment.

Nick

[Gysbert_Wassenaar]

Yes, just like that. No variables involved.