topic Get 1st, 2nd value of a Getfieldselections in QlikView

Get 1st, 2nd value of a Getfieldselections

Wed, 03 Aug 2011 12:01:29 GMT

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

Re: Get 1st, 2nd value of a Getfieldselections

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 12:20:19 GMT

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

Get 1st, 2nd value of a Getfieldselections

Wed, 03 Aug 2011 13:48:09 GMT

Thanks Miguel,

exactly wath I want, and without variable...

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

Regards

Laurent

Re: Get 1st, 2nd value of a Getfieldselections

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 14:00:33 GMT

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

Get 1st, 2nd value of a Getfieldselections

Wed, 03 Aug 2011 14:29:33 GMT

Thanks for that precision!