topic Help with an If Statement Please in QlikView

Help with an If Statement Please

Fri, 15 May 2015 07:48:42 GMT

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

Re: Help with an If Statement Please

nico_ilog — Fri, 15 May 2015 08:25:10 GMT

Hi Nick,

How many days in your week? 5 or 7?

Rgds

Re: Help with an If Statement Please

MarcoWedel — Fri, 15 May 2015 08:38:19 GMT

maybe

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

could be a solution?

regards

Marco

Re: Help with an If Statement Please

Fri, 15 May 2015 09:22:25 GMT

Hi Nicok,

7 days.

Thanks,

Nick

Re: Help with an If Statement Please

Fri, 15 May 2015 09:25:45 GMT

Thanks Marco but that doesnt work Im afraid.

Nick

Re: Help with an If Statement Please

Anonymous — Fri, 15 May 2015 09:37:46 GMT

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

Re: Help with an If Statement Please

nico_ilog — Fri, 15 May 2015 09:54:07 GMT

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?

Re: Help with an If Statement Please

MarcoWedel — Fri, 15 May 2015 10:19:03 GMT

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