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 

[Anil_Babu_Samineni]

Tell us the exact expression for variable? "$(Categoria_Label)"

[ecabanas]

Hi,

The variable is build concatenating text and the category value:  = 'Categoria_' & Catlevel2

[Vegar]

Are you sure you only have one available value for Catlevel2?  

Depending on the definition of your variable you can try adjusting it like this:

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

or like this:

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

[ecabanas]

Hi @Vegar 

Did not works, thank's

[Vegar]

Are you sure that your variable is producing the field name that you want to use? Try displaying the output of the variable in a Diagram object. 

[ecabanas]

It seems is ok:

[Vegar]

In your example that is working you are reffering to Cerveza_754, but in the picture you are presenting Categoria_754. Could this be the issue?

[dwforest]

Variables can take parameters, so maybe: 

vCerveza

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

=($(vCerveza(754)) 

=($(vCerveza(800))

[ecabanas]

Hi @dwforest 

I'm so sorry, but I don't understand what you want to say...why you use Cerveza_$1? what does it mean?

Really apreciate your help

Eduard