Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Partner
Partner

replicate this excel logic in qlikview

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

I have the following table in qlikview:

USERIDRolePerfilAdministrativosAprobadoresConsultores ExpressConsultores IntensosExpertos DigitalesFricción AutenticaciónMonetariosPreparadores
Total  6.62%6.92%46.53%3.20%4.02%9.97%7.65%15.08%
U1T1  1.11%3.33%   95.56% 
U2T2   1.25%   98.75% 
U3T1   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:

2019-03-06_16-34-40.jpg

any idea?

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
lorenzoconforti
Contributor III

Re: replicate this excel logic in qlikview

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

5 Replies
tincholiver
Contributor II

Re: replicate this excel logic in qlikview

Can you share some data?
preferably the table from which you take the data
Highlighted
lorenzoconforti
Contributor III

Re: replicate this excel logic in qlikview

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

Partner
Partner

Re: replicate this excel logic in qlikview

It worked!! thanks
Partner
Partner

Re: replicate this excel logic in qlikview

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
Contributor III

Re: replicate this excel logic in qlikview

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

 

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