Set Analysis Expression Help

[#1]

Month_Year is in the format 'MMM-YYYY'

vYearStart = Date(YearStart(Today()),'MMM-YYYY') which results in Jan-2012

vCurrMonth = =Date(QuarterEnd(Today()),'MMM-YYYY') which results in Jun-2012

What is the right syntax to get sum of values between Jan and Jun 2012

I am writing the expression

Sum({<Month_Year={">=(vYearStart)<=(vCurrMonth)"}>} Value)

Please enlighten me if i am missing something.

[Anonymous]

Try this syntax to start:
Sum({<Month_Year={">=$(vYearStart)<=$(vCurrMonth)"}>} Value)

I am assuming you are setting the variables with the expression, and not a LET statement in the script. But either way, the above should work.
-Phil

[#3]

I am setting my variables in Variable Overview.

I have tried the above expression.

The result is 0.

What could be the issue here?

[swuehl]

How have you created Month_Year?

Using the textual representation of dates or time periods like above often show issues, at least it seems to be not easy to always match the required format. Thus I would recommend using a date type field in your set expression and then use variables or dollar sign expansions that evaluate to numericals, trying to prevent textual date format issues.

[erichshiino]

I agree with the problems with the textual represention but I recommend you convert your fields and variables to an integer representation.

Thus,

Your Month_Year could be used to create another field: nMonthYear

Load...

num( Monht_Year) as nMonthYear

The variables will become:

vYearStart = num(YearStart(Today()) )

vCurrMonth = num(QuarterEnd(Today()) )

Set analysis will be: Sum({<nMonthYear={">=$(vYearStart)<=$(vCurrMonth)"}>} Value)

Hope this helps,

Erich

[#6]

I would try putting your search criteria in a text box to make sure it is evaluating correclty:

">=(vYearStart)<=(vCurrMonth)"

And as Phil Bishop stated, you need to add dollar signs ($) in front of your variable parenthesis (i.e. $(vYearStart), $(vCurrentMonth))

[vgutkovsky]

Make sure both variables start with an '=' in the Variable Overview. Then Phil's expression should work. If it doesn't, try this one:

Sum({<Month_Year={">='$(vYearStart)'<='$(vCurrMonth)'"}>} Value)

(single quotes around the dollar-sign expansion sometimes helps)

Regards,

Vlad