Help with an If Statement Please

[#1]

Hello fellow Qlikers!!!!! 

I am using the if statement below to pull through last weeks data.

if(inweek("Activity_Date", today(), -1), Metric_Value, 0) as LW_Ind

Would someone be able to use the above in some way and provide an if statement so that I can pull through a weeks dat from four weeks ago? So I am excluding the past 3 weeks and pulling through a full week only from 4 weeks back only.

Any help much appreciated as I am a little clueless of where to go from here.

Using a number of metric values the if statement seems the way to go as I can apply it to all of the expressions in the dashboard.

Many thanks in advance,

Nick

[nico_ilog]

Hi Nick,

How many days in your week? 5 or 7?

Rgds

[MarcoWedel]

maybe

if(inweek("Activity_Date", today(), -4), Metric_Value, 0) as LW4_Ind

could be a solution?

regards

Marco

[#4]

Hi Nicok,

7 days.

Thanks,

Nick

[#5]

Thanks Marco but that doesnt work Im afraid.

Nick

[Anonymous]

Hii Nick,

The Function is like this:

InWeek (date, basedate , shift [, weekstart])

returns true if date lies inside the week containing basedate.The week can be offset by shift. Shift is an integer, where the value 0 indicates the week which contains basedate. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekstart.

So by using your function if(inweek("Activity_Date", today(), -1), Metric_Value, 0) as LW_Ind

you are Checking the Activity Date from last week from today because of -1 in third parameter.

so If you want to compare the date from last four weeks the you should use like:

if(inweek("Activity_Date", today(), -4), Metric_Value, 0) as

Thanks & Regards

Prince Anand

[nico_ilog]

Nick,

working on the assumption that you have 7 days a week. Dynamically going back 3 and 4 weeks repectivly, you could do this?

if(("Activity_Date"<= Date(today() -21)) AND ("Activity_Date" >= Date(today() -28)),Metric_Value,0) as FourBack_Ind

See Image

Lemme know?

[MarcoWedel]

Hi,

I tried to create an example using this expression:

table1:
LOAD *,
    Week(Activity_Date) as Week,
    If(InWeek(Activity_Date, Today(), -1), Metric_Value, 0) as LW_Ind,
    If(InWeek(Activity_Date, Today(), -4), Metric_Value, 0) as LW4_Ind;
LOAD Date(Today()+RecNo()-50) as Activity_Date,
    Ceil(Rand()*100) as Metric_Value
AutoGenerate 50;

Can you describe what different result you expect?

thanks

regards

Marco