Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try the following expression:
Sum({<[Quote Month] ={"<=$(= Num(month(AddMonths(now(0), -6))))"}>} [Quote value])
... and let me know
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?
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.