Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

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
Valued Contributor

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

7 Replies
Not applicable

Re: Set analysis greater than less than

Would you be able to post a sample qvw?

mov
Esteemed Contributor III

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.

Not applicable

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.

eduardo_sommer
Valued Contributor

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

Not applicable

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

Not applicable

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

eduardo_sommer
Valued Contributor

Re: Set analysis greater than less than

Your thank you is enough, hahaha

Eduardo

Community Browser