Sum of a column usign dynamic variables based on other fields

[kazamabriel]

Hi, I have a table with these kind of data:

LOAD * INLINE [
COD_DISCIPLINA,  CURSO, SERIE, CARGA_HORARIA

6DEF082,  PPP_2015, 1, 90
6DEF084,  PPP_2015, 1, 65
6DEF085,  PPP_2016, 2, 60

];

I also do have a few variables such as:

Let vPPP_2015_1			 	=	2 ;
Let vPPP_2015_2			 	=	1 ;

 These variables are going to be changed by the user in the app later, so they may be 1, 2, 10, etc.

My goal here is to perform a SUM of the CARGA_HORARIA field multiplied by the variable that corresponds to its CURSO and SERIE values concatenated. So, for the first two lines it should multiply CARGA_HORARIA by vPPP_2015_1. For this I created a measure with the following expression:

SUM(CARGA_HORARIA * $(=('v' & CURSO & '_' & SERIE)))

 Using the first line as example, it should evaluate the expression to

SUM(CARGA_HORARIA * $(=('vPPP_2015_1))

I noticed that it returns an error given because there are multiple values for CURSO and SERIE. If there is a single one for each, it will work smoothly, but not when there are multiple for either one of them.

[marcus_sommer]

What do you want to do isn't possible. Because you will need a $-sign expansion to extract the various field-information and building from a valid variable/field-reference. Such $-sign expansion creates an adhoc-variable which means that they could have only one value at a time - and this is evaluated ones before the object is calculated and applied to each occurring.

This means you will need a different approach, maybe applying multiple if-loops with a more or less hard-coded logic or maybe switching the variables to field-values and then doing the row-level assigning within aggr() constructs. With field-values the users couldn't apply each value but they could get a certain amount of choose able values.