7 Replies Latest reply on Nov 16, 2017 6:07 AM by Norbert Maijoor

# Aggregate function is already an aggregation, and cannot be further aggregated

Hi everyone,

Extend the discussion Re: Grand Total Does NOT Update With Parameter Top Filter (thank you Jonathan Drummey for the help on that one) with a new challenge.

I can't figure this one out, and I really would appreciate any hint or suggestion.

Attached my package workbook sample.

I'm trying to use a calculation from this previous discussion to create a column that would give the group range where the total belongs.

I attempted to create a column where:

BIN1 >> TOTAL = 0 ~ 400

BIN2 >> TOTAL = 400 ~ 600

BIN3 >> TOTAL = 600~1000

Ideally,

IF { FIXED [Master ID]: SUM([Amount in Top N]) } >= 0 AND

{ FIXED [Master ID]: SUM([Amount in Top N]) } <= 400

THEN 'BIN 1'

ELSEIF { FIXED[Master ID]: SUM([Amount in Top N]) } > 400 AND

{ FIXED [Master ID]: SUM([Amount in Top N]) } <= 600

THEN 'BIN 2'

ELSEIF { FIXED [Master ID]: SUM([Amount in Top N]) } > 600 AND

{ FIXED [Master ID]: SUM([Amount in Top N]) } <= 1000

THEN 'BIN 3'

END

but getting an error message below:

(Aggregate function is already an aggregation, and cannot be further aggregated)

When I went straight to the Amount, the result was incorrect

IF { FIXED [Master ID]: SUM([Amount]) } >= 0 AND

{ FIXED [Master ID]: SUM([Amount]) } <= 400

THEN 'BIN 1'

ELSEIF { FIXED[Master ID]: SUM([Amount]) } > 400 AND

{ FIXED [Master ID]: SUM([Amount]) } <= 600

THEN 'BIN 2'

ELSEIF { FIXED [Master ID]: SUM([Amount]) } > 600 AND

{ FIXED [Master ID]: SUM([Amount]) } <= 1000

THEN 'BIN 3'

END

correct result should be 'BIN 2'

Any suggestion?

THANK YOU

• ###### 1. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Hi Thiago,

Not sure but find my approach as reference below and stored in attached workbook version 10.4 located in the original thread.

Regards,

Norbert

• ###### 2. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Thank You Norbert Maijoor

but unfortunately, it doesn't help me.

Your M1 and M2 calculation break the most crucial part of my project - Archive ID and ID Name may represent the same group. For example, on the screen below, Archive ID 2752305 and ID Name 309779339 belong together. That's why it was created a Master ID to group them.

Correct:

Then, thanks to the post Customizing Grand Totals – Part 2 | Drawing with Numbers  from Jonathan Drummey , the Grand Total reflects the top 1 value of the group. Eliminating the 'duplicate ones'.

Correct Grand Total = 413 (screenshot)

I was hoping, Tableau calculation would look at the grand total of 413 and give me back BIN 2 in the column BIN (BIN2 >> TOTAL = 400 ~ 600)

My apologies if my goal was not clear at first. I appreciate any help on this. Thank you

• ###### 3. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Hi Thiago,

Find my updated workbook as reference below and stored in  attached in version 10.4 located in the original thread.

1. D1. Master ID: {Fixed [ID Name]:min([Archive ID])}

2. M1. Amount: if [M4. index()]<=[Parameter TOP N Lenght] and attr({fixed [D1. Master ID]:min([ID Name])})=attr([ID Name]) then sum([Amount])/count([ID Name]) END

3. M2. Grand Total: window_sum(if [M4. index()]<=[Parameter TOP N Lenght] and attr({fixed [D1. Master ID]:min([ID Name])})=attr([ID Name]) then sum([Amount])/count([ID Name]) END)

4. M3. Display: [M4. index()]<=[Parameter TOP N Lenght]

5. M4. Index: Index()

6. Drag required objects to the indicated locations and set Display on Filter to True

Regards,

Norbert

• ###### 4. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Thank You Norbert Maijoor

I successfully replicate your recommendations to my data. However, I still didn't understand what your suggestion to assign the bin value to the Grand Total was.

BIN1 >> TOTAL = 0 ~ 400

BIN2 >> TOTAL = 400 ~ 600

BIN3 >> TOTAL = 600 ~ 1000

I went back to your first shared file and replicated your previous calculation.

D2. BIN

if {fixed [D1. Master ID]:sum([Amount])}>0 and {fixed [D1. Master ID]:sum([Amount])}<=400 then "BIN1"

elseif {fixed [D1. Master ID]:sum([Amount])}>400 and {fixed [D1. Master ID]:sum([Amount])}<=600 then "BIN2" END

but still, no success (it should be BIN2)

Are there any adjustments of your BIN calculation from the first document to the second that I need to do?

Thank you again for all the attention on this.

Attached my worksheet.

• ###### 5. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Hi Thiago,

Find my updated workbook attached

M4. BIN:

if [M2. Grand Total]>0 and [M2. Grand Total]<=400 then "BIN1"

elseif [M2. Grand Total]>400 and [M2. Grand Total]<=600 then "BIN2" END

Regards,

Norbert

• ###### 6. Re: Aggregate function is already an aggregation, and cannot be further aggregated

THANK YOU

• ###### 7. Re: Aggregate function is already an aggregation, and cannot be further aggregated

Hi Thiago,

Perfect!:) Now it's time for a real HEINEKEN with of without alcohol;)

Thanks for the Badge. Appreciated.

Regards,

Norbert