Comparing Maximum Values using maxOver()

Question & Answer
, , ,
1

I need to compare the maximum order amount for each client in a list. Using the maxOver() function, I am able to calculate the maximum order amount value, but I am forced to display the ‘Order ID’ that is used in the calculation or an error is returned (VISUAL_CALC_REFERENCE_MISSING). Using the screenshot from the maxOver() documentation page, I want to display the Customer Region and the maxOver value but not the Service Line or Billed Amount. How can this be accomplished?

image
image589×381 72.6 KB

2

Hi @bodonnell,
You can hide the fields that you do not want to display:

image
image354×480 22.9 KB

Then you can apply a filter to limit any duplicates that are returned.

An alternate option you could explore, you could setup a denseRank calculated field that ranks the values by their order amount. Then you can set a filter to only return to the top 1 result.

Let me know if either of these options works for your case or if you have any additional questions.

3

Thanks for the response Brett. As you pointed out, simply hiding the column doesn’t limit the rows returned, so what does this filter look like? How can I filter Product to only return one row per Region? Even the denseRank option can potentially return multiple rows per Region.

4

Update - Using a runningCount() function instead of a rank function will eliminate the possibility of having multiple matching rows. This is still a very limited solution as you will not be able to display the Min and Max values of a measure within the same visual, as the running count function requires the value to be sorted ascending or descending.

5

Hi @bodonnell,
Is this closer to what you’re looking to achieve?
https://community.amazonquicksight.com/t/comparing-maximum-values-using-maxover/43034

To get one row for each value that provides the max and min, you can add 2 of the sales fields and change the aggregation type for each, one for max and one for min.

6

I need to sum a measure to one level and then determine the min, avg, median, and max at a higher level, so the aggregation type doesn’t help. For example, I have 10 clients, each have a number of locations with a number of employees in each. I want to show the largest location (most employees) for each client. So I need to sum the employees at the location level and then get the max of those values for each client. I found a solution by nesting the ‘over’ calculations, sumOver at the location level and then maxOver at the client level. I think nesting these functions is a new capability as these failed in the past. This works for Min, Avg, and Max, but not for Median as there’s no medianOver function and so far nesting of calcs is not working.

7

Hi @bodonnell,
I can mark this as a feature request for AWS to consider the addition of a medianOver function.

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

8

Hi @bodonnell,
While I’ve marked this as a feature request already and we have not heard further, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!