If and And funtion

[#1]

Hi all,

Please can someone explain how can i use compute a column using "if" & "and" function in QlikView?

below is my script, when i load if i am encoutering an error:

if(day(CalDate )<9, 'Week 1',

  if(day(CalDate ) >=9 and day(CalDate )<=16,'Week 2',

  day(CalDate )>=17 and day(CalDate )<=24,'Week 3','Week 4')) as [Week]

jagan‌ please could you help me with this issue?

Regards,

Karthik

[sunny_talwar]

May be this:

If(Day(CalDate) < 9,   'Week 1',

If(Day(CalDate) < 17, 'Week 2',

If(Day(CalDate) < 25, 'Week 3', 'Week 4'))) as Week

[swuehl]

Just for interest, are your weeks lasting 8 days? I hope you have a longer weekend then 😉

[jagan]

Hi,

Check this, you missed one If() in the third line.

if(day(CalDate )<9, 'Week 1',

  if(day(CalDate ) >=9 and day(CalDate )<=16,'Week 2',

if(day(CalDate )>=17 and day(CalDate )<=24,'Week 3','Week 4'))) as [Week]

Instead of this you can simply use

Ceil(Day(CalDate)/8) AS Week

Hope this helps you.

Regards,

jagan.

[#5]

Thank You sunny It works, What i don't get is in the statement it is clear that anything less than 9 is week 1 and anything less than 17 is week 2.

For example : day 8 i  would expect to see as week 2 instead of week 1 as there is no and statement to filter. please could you explain how this is working.

I might a but thick in understanding the login.

Regards,

KK

[#6]

that is right our weeks are lasting for 8 days but we have only 2 days as weekend not 3

It is the way we are calculating weeks in our organisation, and there is some business logic behind it.

Regards,

KK

[#7]

Thank you Jagan garu, I see one if  clause is missing.

and Ceil function works too.

Regards,

Karthik

[sunny_talwar]

Currently, the if statement is structured in a way that if it doesn't meet the first condition, it will go into the second bucket. But if it does meet the first condition, it will never go to the second condition. I am not sure if you want 8 days to be Week2, then we will have to change the script, but currently 8 will be less than 9 so it will be part of Week1. To make it part of week 2, we can do this:

If(Day(CalDate) < 8,  'Week 1',

If(Day(CalDate) < 17, 'Week 2',

If(Day(CalDate) < 25, 'Week 3', 'Week 4'))) as Week

[#9]

Thank You Sunny.