3 Replies Latest reply: Sep 2, 2014 10:10 AM by Piet Hein van der Stigchel RSS

    Set analysis with variable modifier

      Hi all,

      I need you help in trying to find an expression that allows me to calculate the revenues for last month. I was thinking to use set analysis to do this, and it actually work when I put '08' in the formula.

       

      =sum({<MONTH = {08}>} Revenues)   

       

      The problem is that '08' needs to be a variable according to todays date; it should look something like this:

       

      =sum({<MONTH = {(monthname(today(),-1)}>} Revenues)     

       

      But it isn't working :-(

      Can somebody help??

      Tx

        • Re: Set analysis with variable modifier
          Jonathan Poole

          If you put  ' monthname(today(),-1))'  in a text box does it return 08 as well ?  That is key. Once you get the right expression you can use syntax like the following in the SET STATEMENT:


          sum({<MONTH = {' $(= monthname(today(),-1)) '}>} Revenues)

          • Re: Set analysis with variable modifier
            Manish Kachhia

            Try this.....

             

            if MONTH is in MMM format.... i.e. Jan, Feb, Mar etc...

             

            =SUM({<MONTH = {"$(=SubField('$(MonthNames)',';',Month(Today())-1))"}>}Revenues)

             

            or  if MONTH is in Numeric Format... i.e. 1, 2, 3 etc

            SUM({<MONTH = {"$(=Month(Today())-1)"}>}Revenues)

            • Re: Set analysis with variable modifier
              Piet Hein van der Stigchel

              First of all, your syntax is missing the dollar sign expansion syntax (see help), you need to add $(= and a closing bracket.

              Then you need to get the format of the equation equal, compare numbers with numbers or text with text. If Month is a text or a date field it will expect text on the right hand. In that case you will need to add quotation marks around, so '$(=....)'

              Now you make sure the text is in the same format as the month field, if it's a date use the date function to format e.g.

              sum({<MONTH= {'$(=Date(monthname(today(),-1),'MMM-YY'))'}>} Revenues)

              you will need to adjust the 'MMM-YY' to your own date format