How to display Winner of final and other details when there is no field other than Max(Date) specifying given match as final ?

[shekhar_analyti]

Hi All ,

How to display below in text box using attached data set ?

In Year xxxx final was played between  Team X and Team Y where Winning Team was x or y with man of match as XorY  from team XorY .

NOTE: RECORDS HIGHLIGHTED IN YELLOW ,  IN ATTACHED DATA SHEET REFERS TO FINAL EVENT

Thanks & Regards

Shekar

[hector_munoz]

Hi Shekar,

Try this formula:

='In Year ' & Year & ' final was played between Team ' & FirstSortedValue(team1, -date) & ' and Team ' & FirstSortedValue(team2, -date) & ' where Winning Team was ' & FirstSortedValue(winner, -date) & ' with man of match as ' & FirstSortedValue(player_of_match, -date)

, note that one year must be selected. Also I donot know what field informs of the 'man of the match' team so the last part of the sentence is pending...

Hope it serves!

Regards,

H

[tresesco]

Like this?

[shekhar_analyti]

Hi Hector ,

Thanks for reply . Actually column M(excel data) refers to 'man of the match .

[hector_munoz]

Yes Shekhar, but in what team does the 'man of the match' play?

[shekhar_analyti]

As mentioned , above expression requires date be selected .

But requirement is that when no Year is selected by user then qlik by default should show me data of year=min(year) , but when user selects some year then , that should be reflected . 

[Anil_Babu_Samineni]

I am sure, this is not the right way at all. From your data you need highlighted yellow rows only. Title seems different. May be this?

Aggr(FirstSortedValue(player_of_match,-Aggr(Max(date),player_of_match)), Year)

[shekhar_analyti]

Yeah , i have a separate data which has all data regarding team and its player . I will be able to get it now . Let me try that .

[hector_munoz · Accepted solution]

Then:

='In Year ' & Min(Year) & ' final was played between Team ' & FirstSortedValue({<Year={$(#=Min(Year))}>} team1, -date) & ' and Team ' & FirstSortedValue({<Year={$(#=Min(Year))}>} team2, -date) & ' where Winning Team was ' & FirstSortedValue({<Year={$(#=Min(Year))}>} winner, -date) & ' with man of match as ' & FirstSortedValue({<Year={$(#=Min(Year))}>} player_of_match, -date)

[shekhar_analyti]

It is good learning for me .. Take a bow