1 2 3 Previous Next 30 Replies Latest reply on Jul 31, 2015 2:39 AM by nick Rao

# Max and min of an aggregate

Attached you'll see an example.

I grouped all the customers by 25% of total sales.

So group 1 shows customers for the top 25% of sales.

Next I have the count of the customers in the top 25% of sales.

Finally I show the average sales for the customers in the top 25% of sales.

What I'd like to do next if just show the max and min for the customers in each group.

So for the top 25% of sales, what is the max/min amount a customer spent?

I can't figure out how to do this. Thank you for your assistance.

• ###### 1. Re: Max and min of an aggregate

Hi Julianna,

I think that the only thing you need to do is just to add MIN(Sales) and MAX(Sales) in the list of Measure Values in your table.

Verify if that is correct in the workbook attached, please.

Best,

Ramon

• ###### 2. Re: Max and min of an aggregate

Ramon,

You appear to be right. I tried to re-create something complex but I made it too simple. I need to work on re-creating the problem I have and I will re-submit it here.

• ###### 3. Re: Max and min of an aggregate

My pleasure.

It seems I didn't understand your problem. Let's see your next description of it.

Best,

Ramon

• ###### 4. Re: Re: Max and min of an aggregate

Thanks again for your response. I'm back at it and this time I think I've successfully re-created my problem.

I'd like to know how to determine the min/max of the states within the groups I've made in this file. Finding the average is simple enough, but I'm not sure how to display the min/max of the group.

Thanks!

• ###### 5. Re: Max and min of an aggregate

If your problem is that you cannot bring another copy off [Sales]  on the Measure shelf , you can create 2 calculated fields  MIN(Sales) =  MIN([Sales])  and MAX(Sales) =  MAX([Sales]) , and bring those on the Measure shelf.

Michel

• ###### 6. Re: Max and min of an aggregate

They can be brought onto the shelf, but are the numbers accurate?

• ###### 7. Re: Max and min of an aggregate

No they aren't accurate. They need to be the min/max for the states, not the individual records.

• ###### 8. Re: Max and min of an aggregate

Right, that's what I mean.  There's a way to get at what you want, I'm sure of that, but I am not sure how to address it off the top of my head.  Someone smarter than me may be able to help!

• ###### 9. Re: Max and min of an aggregate

Hi Julianna,

May be if you can create groups with the help of Calculated field & use that field for finding maximum & minimum will help you as you can not use Groups in calculated field. May be this will help.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 10. Re: Max and min of an aggregate

This just seems overly complicated for something that would take 2 minutes to do in Excel....kind of disappointing.

1 of 1 people found this helpful
• ###### 11. Re: Max and min of an aggregate

I would use 2 extra worksheets and 2 indexes.  I brought them together in a dashboard and in addition to the min and max for each group placed the min and max person (although ties are not included).

We can later hide, or re-arrange redo the 2nd and 3rd ID groups (but they need to be in the detail shelf) to remove redundancies, but this is a start.

(edit) Not sure why the mins are so low in the groups.

(edit 2) I tried it for States, but my method isn't sorting them correctly because the states exist in multiple groups.

• ###### 12. Re: Max and min of an aggregate

Tableau is not meant to do what Excel does.  We're only limited by our imagination and data structure in Tableau.  There's a lot to learn, to be sure, but the two programs are very different.  There are certainly things that each is better for.

• ###### 13. Re: Max and min of an aggregate

Agreed,

After using Tableau for awhile, I understand table calculations and can use them; but it is then very hard to reuse those aggregate calculations back in the workbook.  Very easy to do in Excel.

1 of 1 people found this helpful
• ###### 14. Re: Re: Max and min of an aggregate

Hi Julianna,

It is complicated because you are trying to put aggregates in your table based in two levels of granularity.

Take a look at the attached workbook to see if the workaround I did makes solves your requirement.

Best,

Ramon

1 2 3 Previous Next