topic replicate this excel logic in qlikview in QlikView

replicate this excel logic in qlikview

hectorgarcia — Sat, 16 Nov 2024 21:17:28 GMT

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?

Re: replicate this excel logic in qlikview

tincholiver — Wed, 06 Mar 2019 22:24:17 GMT

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

Re: replicate this excel logic in qlikview

lorenzoconforti — Wed, 06 Mar 2019 23:05:41 GMT

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

Re: replicate this excel logic in qlikview

hectorgarcia — Thu, 07 Mar 2019 15:21:55 GMT

It worked!! thanks

Re: replicate this excel logic in qlikview

hectorgarcia — Thu, 07 Mar 2019 16:24:52 GMT

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%

Re: replicate this excel logic in qlikview

lorenzoconforti — Fri, 08 Mar 2019 14:04:58 GMT

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

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