topic Re: IF Statement error in QlikView

IF Statement error

alec1982 — Thu, 06 Sep 2012 14:57:55 GMT

Hi anybody can help me figuring out what's wrong in this IF statement.

if (ExpenseFrequency ='Monthly' & (Year([Payment Date]) = Year(ExpenseStartDate)) &(month(ExpenseStartDate) = month([Payment Date]))& (Day(ExpenseStartDate)<>Day([Payment Date])),

(([Expense Amount in USD]/Day(MonthEnd([Payment Date])))*(Day(MonthEnd([Payment Date]))-Day(ExpenseStartDate)+1)) ,[Expense Amount in USD]) as [Expense Amount (USD)] ,

Like on one of the records I have:

ExpenseFrequency= Monthly

PaymentDate 2008-10-01

ExpenseStartDate=2008-10-15

[Expense Amount in USD]= 4504.17 (this amount after the calculations in the if satement above should be 2470.03 ).

Thanks,

Re: IF Statement error

Thu, 06 Sep 2012 15:00:12 GMT

replace & with and. & is used to concate strings

Re: IF Statement error

Thu, 06 Sep 2012 15:03:01 GMT

if(

ExpenseFrequency ='Monthly' &

should be

if(

ExpenseFrequency ='Monthly' and

Also for other cases...

Use & for concattenating 2 fields (like load fieldA&'someText'&fieldB)

Use and for combining conditions (like if(1=1 and 2=2 and 3=3,'valueIfTrue','valueIfFalse')

Hope it helps!

Re: IF Statement error

alec1982 — Thu, 06 Sep 2012 15:16:04 GMT

This working perfectly,

do you guys have anyway of simplifying the statement above.

Like if you notice i am checking if Year(X) = Year(Z) and month(X)=Month(Z) and Day(X)<>Day(Z)

let me know and thanks for your help.

Re: IF Statement error

Thu, 06 Sep 2012 20:39:54 GMT

You can replace the year and month with monthname() This is a combination of the month and year

Monthname(X) = Monthname(Z) and Day(X)<>Day(Z)