# Function to calculate aggregate over a range of columns, same as in excel =max(c1:c5)

Function to calculate aggregate over a range of columns, same as in excel e.g. =max(c1:c5).

QuickSightâ€™s max function only accepts on argument and there is no GREATEST function. Itâ€™s not elegant or efficient, but depending on your range size, you can use a nested if statement to achieve your desired output.

c_greatest

``````ifelse(
col1 > col2
and col1 > col3
and col1 > col4
and col1 > col5
and col1 > col6,
col1,
ifelse(
col2 > col1
and col2 > col3
and col2 > col4
and col2 > col5
and col2 > col6,
col2,
ifelse(
col3 > col1
and col3 > col2
and col3 > col4
and col3 > col5
and col3 > col6,
col3,
ifelse(
col4 > col1
and col4 > col2
and col4 > col3
and col4 > col5
and col4 > col6,
col4,
ifelse(
col5 > col1
and col5 > col2
and col5 > col3
and col5 > col4
and col5 > col6,
col5,
col6
)
)
)
)
)
``````

1 Like

This is an interesting request. For the range, like c1:c5, do we have a sorting criteria? Could we do a workaround as maxif(c, rank([sorting ASC], [partition field], PRE_AGG)<=5)?
At the same time, yes, we will consider to support the range agg functions, such as range max. Thanks!

Sorry, I didnâ€™t read it carefully. My above solution is for max over a range of rows. Robâ€™s solution is correct to resolve the question for a range of columns.

1 Like

Thanks @robdhondt. I used similar as response to a question in stackoverflow. Reached here, to understand if there might be something more that can be easy enough.

1 Like