1 2 Previous Next 19 Replies Latest reply on May 23, 2017 12:40 AM by Okechukwu Ossai

# How to find max of max in table summary?

Hi there,

I'd like to make a table to summary number of product on each tester type by finding max quantity of the tester type on each product.

Picture on your left is current report I have and on your right is what I want to have. Not sure is it possible to do?

Anyways, please guide me. I've use Tableau 10.1 for this task.

thank you in advance.

Regard,

Ohm.

• ###### 1. Re: How to find max of max in table summary?

{FIXED [product...]: MAX(QTY)} = {FIXED [Type]: SUM(QTY)}

Filter True

• ###### 2. Re: How to find max of max in table summary?

Hi Daniel,

It's very close I guess. But still cannot solve my problem. Thank you.

• ###### 3. Re: How to find max of max in table summary?

What is your problem?

• ###### 4. Re: How to find max of max in table summary?

{FIXED [product...]: MAX(QTY)} = {FIXED [podut ...], [Type]: SUM(QTY)}

?

• ###### 5. Re: How to find max of max in table summary?

Hi Daniel,

Sorry still not right for my case. I can show you.

Only 1 True I can get here from bottom of the table.

Any clue?

Thank you again.

• ###### 6. Re: How to find max of max in table summary?

Can you place these on the text and send me a screenshot like the one above?

{FIXED [product]: MAX(QTY)}

{FIXED [poduct], [Type]: SUM(QTY)}

{FIXED [Product]: MAX({FIXED [Type]: SUM([QTY])})}

• ###### 7. Re: How to find max of max in table summary?

Hi Preutti,

You can make the summary table by following the steps below.

1. Create a calculated field called [Max of Max Filter]

{FIXED [PRODUCT]: MAX({FIXED [PRODUCT], [TYPE]: SUM([QTY])})} = {FIXED [PRODUCT], [TYPE]: SUM([QTY])}

2. Put [Max of Max Filter] on the filter shelf and set to True.

Let me know if this helps.

Ossai

1 of 1 people found this helpful
• ###### 8. Re: How to find max of max in table summary?

Hi Ossai,

It's working well. Thanks. However, I'm try to do as same as your do on other report that add more WEEK and PFCODE dimension and it's working well as yours. So can you help again. I'm very confused about this FIXED function.

• ###### 9. Re: How to find max of max in table summary?

Yes I can help. What do you want to do with the PFCODE and what is the expected result?

Do you want to return a single row or multiple rows in the PFCODE column?

• ###### 10. Re: How to find max of max in table summary?

Hi Ossai

Thank for helping. It's more easy for me to explain with picture. Please see.

What do you want to do with the PFCODE ?

-> Finding Max of PFCODE from MAX Type. Let's say in each week under PRODUCT, I have at least 4 TYPEs.

I have to find Max TYPEs then under MAX type, I have to find MAX PFCODE.

and what is the expected result?

-> Expected result is Max of PFCODE under Max Product in each week. So there can be a variation of MAX TYPE and/or MAX PFCODE in same product from week to week.

From picture, Max PFCODE of FY1743 should be 29 with 1 PFCODE , Max PFCODE of FY1744 should be 29,968 with other PFCODE.

Do you want to return a single row or multiple rows in the PFCODE column?

-> Able to be multiple row in each product since in each week PFCODE has a chance not to be the same PFCODE, maximum variation of PFCODE row in each product can be up to number of WEEK.

I'm not native English speaker so if I made you confused. Please let me know.

Thank you.

Ohm.

• ###### 11. Re: How to find max of max in table summary?

Okay, here's the combined solution.

1. Create a calculated field called [Max of Max Filter]

{FIXED [PRODUCT]: MAX({FIXED [PRODUCT], [TYPE]: SUM([QTY])})} = {FIXED [PRODUCT], [TYPE]: SUM([QTY])}

2. Put [Max of Max Filter] on the filter shelf and set to True.

3. Create calculated field [Max PFCODE]

IF { FIXED [PRODUCT],[TYPE], [PFCODE]: MAX({ FIXED [PRODUCT],[TYPE], [PFCODE], [WEEK]: SUM([QTY])})} = { FIXED [PRODUCT], [TYPE], [PFCODE], [WEEK]: SUM([QTY])} THEN

{ FIXED [PRODUCT], [TYPE], [PFCODE], [WEEK]: SUM([QTY])} END

4. Set up your view like the image in your last response. However, remove what you have on the Text marks and put [Max PFCODE] there.

Hope this helps.

Ossai

• ###### 12. Re: How to find max of max in table summary?

Hi Ossai,

Thank for your help. It's very close, sorry for my poor explanation. Your logic is right to select to select Max of TYPE. But in the Max of type, all of PFCODE still there but show the value of the Max PFCODE only.

What I'd like to show is 1 MAX PFCODE in FY1743 , 1 MAX PFCODE in FY1744 , 1 MAX PFCODE in FY1745 and 1 MAX PFCODE in FY1746. Should be just 4 PFCODE under TYPE.

Is it possible to do that?

• ###### 13. Re: How to find max of max in table summary?

Let's me explain more by Picture.

• ###### 14. Re: How to find max of max in table summary?

Hi Preutti,

Thank you for explaining what your objective is. This is different from the way your original question was worded but at least I understand what you want now.

Simply, what you want is to find the maximum QTY for each week and then display the Pfcode, Type and Product corresponding to this Max weekly QTY.

1. Create calculated field [Max of Max Filter]

{ FIXED [WEEK], [PRODUCT]: MAX({FIXED [WEEK], [PRODUCT], [TYPE], [PFCODE]: SUM([QTY])})} = {FIXED [WEEK], [PRODUCT], [TYPE], [PFCODE]: SUM([QTY])}

Put [Max of Max Filter] on the filter shelf and set the filter to TRUE.

2. I don't know how your data is aggregated. However, you can simply put SUM(QTY) on the Text marks and that's it!

As long as you've set [Max of Max Filter] as true, either of these 3 measures will give you the correct result when placed on the Text marks. The choice is yours.

i) SUM(QTY)

ii) {FIXED [WEEK], [PRODUCT], [TYPE], [PFCODE]: SUM([QTY])}

iii) { FIXED [WEEK], [PRODUCT]: MAX({FIXED [WEEK], [PRODUCT], [TYPE], [PFCODE]: SUM([QTY])})}

Hope this helps. Let me know if I've missed something and I will gladly revise the solution for you.

Ossai

1 2 Previous Next