Get 1st, 2nd value of a Getfieldselections

[#1]

Hello,

Is it possible to put the 1st, 2nd, ... value from a Getfieldselections to differents variables?

The Goal is to make a comparaison of those different selected values. Like This:

To date 31/07/2011	20050	2210
1 Year	0,7415%	0,5829%
6 months	0,4694%	0,3784%
3 months	0,2825%	0,2226%
Year to date	0,5035%	0,4242%

Where 20050 is the 1st, 2210 the 2nd, ... (if more selected values)

And use the variable (v1)  for the column(1) in place of value "20050" and for column(2) the variable (v2) with the second value of the list. like this:

=(
avg({1<NROTIT={$(v1)},DATE_VALEUR={"$(=Date(vDateRef))"}>} DERVALTIT)
-
if(Valuelist('1 Year','6 months','3 months','Year to date')='1 Year',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-12)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='6 months',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-6)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='3 months',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-3)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='Year to date',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(Yearstart(vDateRef)))"}>} DERVALTIT))
))))
)
/
if(Valuelist('1 Year','6 months','3 months','Year to date')='1 Year',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-12)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='6 months',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-6)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='3 months',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(addmonths(vDateRef,-3)))"}>} DERVALTIT)),
if(Valuelist('1 Year','6 months','3 months','Year to date')='Year to date',top(sum({1<NROTIT={$(v1)},DATE_VALEUR={"$(=date(Yearstart(vDateRef)))"}>} DERVALTIT))
))))

Thanks for your help

Laurent

[Miguel_Angel_Baeyens · Accepted solution]

Hi Laurent,

Try the following code to get the first values from a field

=SubField(Concat(DISTINCT Field, ','), ',', 1)

Change the 1 to 2 to get the second value, and so on.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Thanks Miguel,

exactly wath I want, and without variable...

I wasn't aware that Concat() use also the selected values.

Regards

Laurent

[Miguel_Angel_Baeyens]

Glad to help.

But concat doesn't returns selected values. It rather concatenates all possible values from a given field or expression. That means that, when you don't have anything selected in field "CustomerID" then all CustomerID values are possible, and hence this function will return all of them, while GetFieldSelections will not return anything. If you select only one value, the function still returns the possible values, that are, in this case, only one value.

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

Thanks for that precision!