5 Replies Latest reply on Sep 15, 2016 4:38 AM by Andrew Watson

# Question on finding aggregation (max) based on distinct variables

Hi

I have the following set of data:

 ID Type Count A Cat 2 A Mouse 1 A Cat 4 B Mouse 2 B Mouse 3 C Cat 4 C Mouse 5 C Mouse 3 C Cat 2

I am trying to plot the graphs of the sum of counts at: 1) aggregated ID level, and 2) aggregated for all IDs.

However the complication arises. For type=Cat of the same ID, I need to find the max(count) before I do the aggregation. Else if Type = Mouse, I will aggregate the individual count.

So in the first example, for case ID A, my aggregated count = 4+1 = 5 (since 4=max of 2 and 4 for Type =Cat), for B = 2+3 while C=4+5+3 = 12.

Not sure how I can solve this problem? Sorry I've tried to understand the examples online but it's really confusing. I dont know if I should use windows_max or {fixed : max} or any other functions.

Regards

• ###### 1. Re: Question on finding aggregation (max) based on distinct variables

I don't have tableau on my machine to test but this formula might fix your issue:

IF [Type] = 'Cat' THEN {FIXED [ID],[Type]:MAX([Count])} ELSE [Count] END

Hopefully that formula won't throw up and strange aggregation errors.

You would need to SUM this field. Note you may also need to change FIXED to INCLUDE, it all depends on how you're using it. Also this may actually duplicate the MAX so please watch out for that and if no a rethink will be required. Good luck, I'm interested to hear whether it works.

• ###### 2. Re: Question on finding aggregation (max) based on distinct variables

Hi Andrew

Thanks for your reply. Yep I'm facing the problem you mentioned, i.e. the max was duplicated. I'm not sure how I should use the sum function properly so only one max is counted?

Thanks!

Regards

• ###### 3. Re: Question on finding aggregation (max) based on distinct variables

What about something like: IF [Count] = [test] THEN [Count] END

I suspect that might be too simplistic though, as there haven't been others jumping into this thread with the solution it clearly isn't straightforward :-)

An alternative to try would be:

IF {FIXED [ID], [Type]: SUM([Count])} = [test] THEN [Count] END

Should you get a mixing agg and non-agg error with that it should be solved by wrapping the measures in SUM.

I should download Tableau on to this machine, would make solving these problems far easier...

• ###### 4. Re: Question on finding aggregation (max) based on distinct variables

Hi Andrew

Many thanks for your help! I managed to get it by creating a new variable:

if [test]= [Count] then [Count]

else 0

END

Basically im just forcing one of the records to 0 so when I sum i only sum it once.