Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis greater than less than

I've seen many questions about this and tried the different solutions, but none have worked for me. I'm trying to get sales from the current quarter. Pretty simple. But I either get all sales or 0 sales. I've tried many variations. This is what I currently have:

Sum({$*BM13 <Month={'>= $(=Month(QuarterStart(today()))) <= $(=Month(QuarterEnd(today())))'}>}[Sales Amount])

Any ideas?

1 Solution

Accepted Solutions
eduardo_sommer
Partner - Specialist
Partner - Specialist

I think the problem is that the Month funcion returns string data. So, you cannot use the result in less or greater operators. Use num(month()), instead.

Eduardo

View solution in original post

7 Replies
Not applicable
Author

Would you be able to post a sample qvw?

Anonymous
Not applicable
Author

The syntax you use is sensitive to spaces.  Maybe this will work:
Sum({$*BM13 <Month={'>=$(=Month(QuarterStart(today())))<=$(=Month(QuarterEnd(today())))'}>}[Sales Amount])

I prefer a different approach:
Sum({$*BM13 <Month={{"$(= '>=' & Month(QuarterStart(today())) '<=' & Month(QuarterEnd(today())))"}>} [Sales Amount])

In any case, it is essential that Month and Month(QuarterStart(today())) are in exact same format.

Not applicable
Author

Unfortunately Michael, neither option worked. I still get 0 as a result. If I use only one expression without the greater than less than, I get the expected result:

=Sum({$*BM13 <Month={"$(=Month(QuarterEnd(today())))"}>} [Sales Amount])

But obviously, this doesn't get what I need.

eduardo_sommer
Partner - Specialist
Partner - Specialist

I think the problem is that the Month funcion returns string data. So, you cannot use the result in less or greater operators. Use num(month()), instead.

Eduardo

Not applicable
Author

Your original expression only uses single quotes, should they be double quotes (honestly don't know if there is a difference in this kind of set analysis).

Not applicable
Author

Eduardo, I could kiss ya! That worked. Thank you so much!

=Sum({$*BM13 <Month={'>=$(=Num(Month(QuarterStart(today()))))<=$(=Num(Month(QuarterEnd(today()))))'}>}[Sales Amount])

eduardo_sommer
Partner - Specialist
Partner - Specialist

Your thank you is enough, hahaha

Eduardo