Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

if(and statement

Hello,

I really need help with converting if statement on excel to Qlikview! The formula depicts the logic that if the date falls within 1st to 19th then it should be counted as of the same month, and if the date starts after the 20th then it should be counted as of the next month. Can someone give me pointers on how to achieve this on Qlikview?

formula on excel:

=IF(AND(DAY(C11)>=1,DAY(C11)<20),DATE(YEAR(C11),MONTH(C11),1),DATE(YEAR(C11),MONTH(C11)+1,1))

formula on qlikeview, not working.

=IF(AND(DAY([Opp Est Eng Start Date])>=1,DAY([Opp Est Eng Start Date])<20),DATE(YEAR([Opp Est Eng Start Date]),MONTH([Opp Est Eng Start Date]),1),DATE(YEAR([Opp Est Eng Start Date]),MONTH([Opp Est Eng Start Date])+1,1))

Many thanks!

1 Solution

Accepted Solutions
vishsaggi
Not applicable

Re: if(and statement

Try this ?

= =IF(DAY([Opp Est Eng Start Date])>=1 AND DAY([Opp Est Eng Start Date])<20, Month([Opp Est Eng Start Date]),MONTH([Opp Est Eng Start Date])+1)

3 Replies
vishsaggi
Not applicable

Re: if(and statement

Try this ?

= =IF(DAY([Opp Est Eng Start Date])>=1 AND DAY([Opp Est Eng Start Date])<20, Month([Opp Est Eng Start Date]),MONTH([Opp Est Eng Start Date])+1)

rwunderlich
Not applicable

Re: if(and statement

I don't think you need test for >1, just < 20. Next months date is AddMonths(). So:

IF(DAY([Opp Est Eng Start Date])<20

, MonthStart([Opp Est Eng Start Date])

,MonthStart(AddMonths([Opp Est Eng Start Date],1))

)


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable

Re: if(and statement

This is correct. Thank you!