sum a different field deppending on a variable value

[ecabanas]

Hi Guys,

I'm using a set expression to calculate different things deppending on the value of a variable. For example:

this is the formula:

=(sum({<CalendarYear_C =,CalendarMonthName_C=,CalendarMonthAndYear_C={">=$(=addmonths(max(CalendarMonthAndYear_C),-12)) <=$(=Max(CalendarMonthAndYear_C))"}>}Cerveza_754)

The field to sum is named "Cerveza_754" because we are calculating the category "754". When I selected the category 754, a variable is stored as "754", But I want to sum Cerveza_800 or Cerveza_900 too

I build a variable named "Categoria_Label" that concatenate "Cerveza_"&Category and the Variable result is good, but when I put it in the formula did not works

=(sum({<CalendarYear_C =,CalendarMonthName_C=,CalendarMonthAndYear_C={">=$(=addmonths(max(CalendarMonthAndYear_C),-12)) <=$(=Max(CalendarMonthAndYear_C))"}>}$(Categoria_Label))

Is it possible to do something like I'm trying to do?

Thanks 

[dwforest]

$1 in a variable refers to parameter 1

($(Cerveza(800))) calls the variable Cerveza and passes 800 as the first parameter  (all the parens are necessary btw)

In the variable definition the $1 would be replaced by 800 in this example. Note that the formula editor will say $1 is a bad name and/or that the formula is invalid. Create the formula with a hard coded value initially to validate the syntax, then replace the part to be dynamic with $1.

Of course you can pass more values and they would be $2, $3, etc