Weeks data 4 weeks ago

Thu, 07 May 2015 08:25:02 GMT

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

Re: Weeks data 4 weeks ago

Gysbert_Wassenaar — Thu, 07 May 2015 08:57:22 GMT

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))'}

Re: Weeks data 4 weeks ago

Thu, 07 May 2015 09:15:18 GMT

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

Re: Weeks data 4 weeks ago

Gysbert_Wassenaar — Thu, 07 May 2015 09:33:33 GMT

Yes, just like that. No variables involved.

topic Re: Weeks data 4 weeks ago in QlikView

Weeks data 4 weeks ago

Re: Weeks data 4 weeks ago

Re: Weeks data 4 weeks ago

Re: Weeks data 4 weeks ago