6 Replies Latest reply: May 25, 2012 5:25 PM by Vlad Gutkovsky RSS

    Set Analysis Expression Help

      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.



        • Set Analysis Expression Help
          Phil Bishop

          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.

            • Set Analysis Expression Help

              I am setting my variables in Variable Overview.


              I have tried the above expression.

              The result is 0.


              What could be the issue here?

                • Set Analysis Expression Help
                  Stefan Wühl

                  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.

                    • Set Analysis Expression Help
                      Erich Shiino

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



                      Your Month_Year could be used to create another field: nMonthYear



                      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,



                • Set Analysis Expression Help

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


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

                    • Set Analysis Expression Help
                      Vlad Gutkovsky

                      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)