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).
If this functionality already exists, please share its working.

Hi @Pankaj_Ranga -

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