Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Fetching field values for given set of conditions

Hi All,

I have a following kind of data.

IdNamePercentPass
1R18.4
2R18.5
3R18.8
4R18.7
1R27.8
2R27.9
3R27.4
4R27.5
1R38.5
2R35.8
3R38.3
4R38.2

Name and ID form the composite key.

I trying to achieve the following calculation:

  • First find maximum PercentPass Name wise
    • In this case, it would be : R1 - 8.8, R2 - 7.9, R3 - 8.5
  • Now store that Name in a variable Name_max having maximum PercentPass
    • In this case, it would be R1
  • Store Name in variable Name_max_2 having second maximum PercentPass
    • In this case, it would be R3

I am not able to figure a way to do this.

Please help.

Thanks in advance.

Regards,

Disha Ahuja

1 Solution

Accepted Solutions

Re: Fetching field values for given set of conditions

Try these:

Name_max

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name))

Name_max_2

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 2)


Name_max_3

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 3)

3 Replies
balrajahlawat
Esteemed Contributor

Re: Fetching field values for given set of conditions

try like this for Maximum?

=max(Aggr(max(PercentPass),Name))

Re: Fetching field values for given set of conditions

Try these:

Name_max

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name))

Name_max_2

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 2)


Name_max_3

=FirstSortedValue(DISTINCT Name, -Aggr(Max(TOTAL <Name> PercentPass), Id, Name), 3)

Not applicable

Re: Fetching field values for given set of conditions

Thanks a lot for your help Sunny. This did me wonders!!

Community Browser