Set analysis with variable modifier

[Anonymous]

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

[JonnyPoole]

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)

[MK_QSL]

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)

[stigchel · Accepted solution]

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