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

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

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

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

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