David BERNIER Posted November 25, 2019 Share Posted November 25, 2019 Dear all I have the current set of data: Month Company Average Delay (h) January 2018 AirFrance 2.3 January 2018 Swiss Air 0.2 January 2018 American Airlines 1.1 February 2018 AirFrance 0.5 February 2018 Swiss Air 0.6 February 2018 American Airlines 0.9 March 2018 AirFrance 2.3 March 2018 Swiss Air 1 March 2018 American Airlines 2.5 April 2018 AirFrance 3.5 April 2018 Swiss Air 2.1 April 2018 American Airlines 0.9 I figured out how to calculate the shortest delay obtained for each month, using the "OVER" statement. However, I also need to export the best performing Company for each month, to achieve this result: Month Company Average Delay (h) Shortest Delay within Month Best Performing Company January 2018 AirFrance 2.3 0.2 Swiss Air January 2018 Swiss Air 0.2 0.2 Swiss Air January 2018 American Airlines 1.1 0.2 Swiss Air February 2018 AirFrance 0.5 0.5 AirFrance February 2018 Swiss Air 0.6 0.5 AirFrance February 2018 American Airlines 0.9 0.5 AirFrance March 2018 AirFrance 2.3 1 Swiss Air March 2018 Swiss Air 1 1 Swiss Air March 2018 American Airlines 2.5 1 Swiss Air April 2018 AirFrance 3.5 0.9 American Airlines April 2018 Swiss Air 2.1 0.9 American Airlines April 2018 American Airlines 0.9 0.9 American Airlines I can't figure out how to get the value in the column "Company" while the criteria for selecting this Company is based on another Column. Is there any simple way to achieve this Thanks in advance for your advice, Kind regards David Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted November 27, 2019 Share Posted November 27, 2019 You can use below expression: Max(case when [Average Delay (h)]=Min([Average Delay (h)]) OVER ([Month]) then [Company] end) OVER ([Month])WhereMin([Average Delay (h)]) OVER ([Month]) gets the min average delay over each month case when condition statement will get the company name where avg delay is min and show that company name for each month Link to comment Share on other sites More sharing options...
David BERNIER Posted November 27, 2019 Author Share Posted November 27, 2019 Works like a charm, perfect! Many thanks, I was running crazy with this question. All the best, David Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now