3 weeks previous

[#1]

Hi,

How do we have it so our dashboard looks at 3 weeks previous (week commencing). So in the load script, create a date measure we can use to just pull 3 weeks previous data. At the moment we have a full year and we want it by week (but 3 weekes previous)

Please help!

Thanks,

[Anonymous]

The most straightforward way is to select the previous three weeks.

Another way is to use set analysis in expressions so they calculate the data only for the previous three weeks.

Yet another way is to create a flag in the script, identifying of the date (week) belongs to the previous three or not.  Use this flag as selection or expression condition.

[buzzy996]

see this sample,

[Anonymous]

Hi,

This  used  for  get  3  3 weeks previous

=week(Today())-3

[rakesh_kumar]

Hi Kate,

You can either do it by using set analysis expressions in the charts where you want to display last 3 weeks of data. For example you can use an expression like this:

Count({<Arrival_Date_Time={">=$(=weekstart(max(Date_Field),-4)) <=$(=weekend(max(Date_Field),-1))"}>}Customers)

Another way would be by restricting the data you load into the QVW. For example you could use a where statement during the load like:

Table:

Load * from QVD

where Date_Field >= Today()-21;

Regards,

RK

[#6]

my load looks like this: Can i not use an if to create a measure to use for going 3 weeks previous?

LOAD
CCG,
"fk_calendar" as Calendar_ID,
    "fk_consultant" as Consultant_ID,
    "fk_gp" as GP_ID,
    "fk_metric" as %Metric_ID,
    "fk_outcome_of_attendance" 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,
     0 as weeks_metric,
   0 as weeks_metric_pivot,
     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, // use this for month
    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, // use this for month
    if(inmonth ( "Activity_Date", today(), -2 ), Metric_Value, 0) as PreMon_Activity

Can i not use an if to create a measure to use for going 3 weeks previous?

Thanks,

     

[buzzy996]

in load statement : where Date#(date,'DD/MM/YYYY')>=Date(Num(Date(Now(),'DD/MM/YYYY')-21),'DD/MM/YYYY');

front end: =Date(Num(Date(Now(),'DD/MM/YYYY')-21),'DD/MM/YYYY');

[rakesh_kumar]

Yes you can create a flag using an if statement and you can use this flag in the charts to show just the data for the last 3 weeks. However if your objective was to load only last 3 weeks of data into the QVW then you should use a where condition to restrict the data to just 3 weeks as I told previously.