7 Replies Latest reply: Jun 10, 2014 3:02 PM by Eduardo Sommer

# 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?

• ###### Re: Set analysis greater than less than

Would you be able to post a sample qvw?

• ###### Re: Set analysis greater than less than

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.

• ###### Re: Set analysis greater than less than

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.

• ###### Re: Set analysis greater than less than

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

• ###### Re: Set analysis greater than less than

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

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

• ###### Re: Set analysis greater than less than

Your thank you is enough, hahaha

Eduardo

• ###### Re: Set analysis greater than less than

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).