topic 3 weeks previous in QlikView

3 weeks previous

Wed, 06 May 2015 15:20:40 GMT

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,

Re: 3 weeks previous

Anonymous — Wed, 06 May 2015 15:28:33 GMT

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.

Re: 3 weeks previous

buzzy996 — Wed, 06 May 2015 15:36:08 GMT

see this sample,

Re: 3 weeks previous

Anonymous — Wed, 06 May 2015 15:36:33 GMT

Hi,

This used for get 3 3 weeks previous

=week(Today())-3

Re: 3 weeks previous

rakesh_kumar — Wed, 06 May 2015 15:36:45 GMT

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,

Re: 3 weeks previous

Wed, 06 May 2015 15:49:07 GMT

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,

Re: 3 weeks previous

buzzy996 — Wed, 06 May 2015 15:56:03 GMT

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

Re: 3 weeks previous

rakesh_kumar — Wed, 06 May 2015 15:58:47 GMT

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.