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
)
)
)
)
)
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.
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.