If statement returning null value

Thu, 10 Nov 2011 15:27:18 GMT

Hi guys,

I'm having a bit of a problem with an if statement. What happens is a list box containing a list of month ends is selected. A table then displays values for this month end and the previous 12 as well in different columns. This expression is used to get the month ends for the previous months.

The if statement checks if the amount of months to look back is greater than the amount of months passed that year and if so, minuses a year and sets the month to the correct month in the previous year. The problem I am encountering is that if the date is more than 1 month into the previous year it retuns a null value. If the first section that simply sets the month and doesn't change the year is commented out it works fine.

It seems like because this part of the statement would return a null value if run on a date where not enough months were left in the year it is stopping the expression from working, even though in those instances the else section should be running.

=If(([Month]-7)>1,
MonthEnd(Sum({$<[Display Year]={$(=[Year])}, [Display Month]={$(=[Month]-7)}, [Display Day] = {1}>}[Invoice Date]))
,
MonthEnd(Sum({$<[Display Year]={$(=[Year]-1)}, [Display Month]={$(=[Month]+5)}, [Display Day] = {1}>}[Invoice Datel]))
)

topic If statement returning null value in QlikView

If statement returning null value