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

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

                       

                      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

                • Set Analysis Expression Help

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

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

                       

                      Regards,

                      Vlad