topic sum a different field deppending on a variable value in App Development

sum a different field deppending on a variable value

ecabanas — Wed, 06 Nov 2019 09:55:18 GMT

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

Re: sum a different field deppending on a variable value

Anil_Babu_Samineni — Wed, 06 Nov 2019 10:00:13 GMT

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

Re: sum a different field deppending on a variable value

ecabanas — Wed, 06 Nov 2019 11:00:59 GMT

Hi,

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

Re: sum a different field deppending on a variable value

Vegar — Wed, 06 Nov 2019 11:21:25 GMT

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))])

Re: sum a different field deppending on a variable value

ecabanas — Wed, 06 Nov 2019 11:29:08 GMT

Hi @Vegar

Did not works, thank's

Re: sum a different field deppending on a variable value

Vegar — Wed, 06 Nov 2019 11:41:06 GMT

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.

Re: sum a different field deppending on a variable value

ecabanas — Wed, 06 Nov 2019 11:46:40 GMT

It seems is ok:

Re: sum a different field deppending on a variable value

Vegar — Wed, 06 Nov 2019 12:46:50 GMT

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?

Re: sum a different field deppending on a variable value

dwforest — Wed, 06 Nov 2019 20:56:45 GMT

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))

Re: sum a different field deppending on a variable value

ecabanas — Thu, 07 Nov 2019 11:06:42 GMT

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

Re: sum a different field deppending on a variable value

dwforest — Thu, 07 Nov 2019 17:43:27 GMT

$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