Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
I am setting my variables in Variable Overview.
I have tried the above expression.
The result is 0.
What could be the issue here?
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.
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
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))
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