topic Re: trouble with less than equal in set analysis in QlikView

trouble with less than equal in set analysis

Mon, 27 Jan 2014 12:47:52 GMT

HI,

I want to fetch quote values that are six months old or older than it. I am using the following expression

=Sum({<[Quote Month] ={"<=$(= month(AddMonths(now(0), -6)))"}>} [Quote value])

Its not working. Can someone please fix the mistake in this expression

Arif

Re: trouble with less than equal in set analysis

alexandros17 — Mon, 27 Jan 2014 12:55:58 GMT

Try the following expression:

Sum({<[Quote Month] ={"<=$(= Num(month(AddMonths(now(0), -6))))"}>} [Quote value])

... and let me know

Re: trouble with less than equal in set analysis

tresB — Mon, 27 Jan 2014 13:01:31 GMT

Usually, this type of issues cause because of format issue. How is your data in [Quote Month], numeric or string? Is that created using month() function in the script?

Re: trouble with less than equal in set analysis

Colin-Albert — Mon, 27 Jan 2014 13:14:10 GMT

How is your [QuoteMonth] data held? If it is just a month name then your expression will not work.

The Month function in QlikView will return a dual data field. Jan, 1; Feb, 2; Mar, 3...Dec,12 with the default month start. The current month January, so your comparison will look for any orders with a month value less than July (7).

Your comparison really needs to compare date values to select quotes over 6 months old.

=Sum({<[Quote Date] ={"<=$(= date(floor(AddMonths(now(0), -6))))"}>} [Quote value])

The floor function strips the time component from the date.

This function will also require the [Quote Date] value to be held as a QlikView date, not simply text.

Using date(source_date_field) as [Quote Date] in your load script will do this.