replicate this excel logic in qlikview

[hectorgarcia]

Hi I ned to replicate the following excel logic into qlikview.

I have the following table in qlikview:

USERID	Role	Perfil	Administrativos	Aprobadores	Consultores Express	Consultores Intensos	Expertos Digitales	Fricción Autenticación	Monetarios	Preparadores
Total			6.62%	6.92%	46.53%	3.20%	4.02%	9.97%	7.65%	15.08%
U1	T1			1.11%	3.33%				95.56%
U2	T2				1.25%				98.75%
U3	T1				98.96%			1.04%

I need to add a column that show the name of the column for the max value for each user.

In excel I do it using this combination of functions:

any idea?

[tincholiver]

Can you share some data?
preferably the table from which you take the data

[lorenzoconforti · Accepted solution]

Are your columns (from Administrativos to Preparadores) values of the same dimension? i.e. do you have a field in your data that contains, as values, those headers? If that's the case, you could achieve what you are looking for with something like this (where PercentageType is your dimension and #PercentageValue your values)

=FirstSortedValue(PercentageType ,-aggr(Sum(#PercentageValue ),CostType),1)

If your data is not structured that way, you can edit your loading script with a CrossTable to make a bit easier to work with

[hectorgarcia]

It worked!! thanks

[hectorgarcia]

quick question, any idea when you have to max value on several columns?, with the expression you suggested is returning null.

USERIDPatron NombrePerfil Administrativos Consultores Express Consultores Intensos Consultores Intermedios Expertos Digitales Fricción Autenticación Monetarios Preparadores
1017223612ME 4.15% 36.33% 3.46% 5.54% 1.38% 5.54% 7.27% 36.33%

[lorenzoconforti]

You could use DISTINCT but it's only going to give you one, not both

=FirstSortedValue(DISTINCT PercentageType ,-aggr(Sum(#PercentageValue ),CostType),1)