1 2 Previous Next 18 Replies Latest reply on Apr 19, 2012 12:43 AM by Willem Botha

Aggregate and Non Aggregate in IF Statements

Hi All,

I will get my head around this eventually, however I am stuck trying to make this formula work, I have tried all different types of combinations, taking the SUM off, adding it in etc. However I cannot get rid of the Aggregate and Non Aggregate error message. Any help with this would be much appreciated.

Heres the formula

IF[REGION] = 'REGION A'

THEN ([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Region Price])

ELSE ([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Price])

END

Many thanks

Chris

• 1. Re: Aggregate and Non Aggregate in IF Statements

Basically, if the expression contains any aggregates then all fields must be aggregates.  This goes for fields in the IF condition as well as in the actual result expression.

Often when you don't think you need an aggregate, the answer is to wrap the field in question in an ATTR() function.  Other times you may need to use AVG() or even MIN() or MAX().

Without knowing your data or how your workbook is laid out this is just a guess, but you could try this:

IFATTR([REGION]) = 'REGION A'

THEN ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*ATTR([New Region Price]))

ELSE ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*ATTR([New Price]))

END

• 2. Re: Aggregate and Non Aggregate in IF Statements

Thanks Richard

I have pasted in your formula and I am gettng a syntax error at the beginning of the THEN - it says maybe you are missing an operator.

Any ideas?

Thanks again

Chris

• 3. Re: Aggregate and Non Aggregate in IF Statements

Sorry, I didn't notice where your brackets were - and also missed a space.  Try this:

IF ATTR([REGION]) = 'REGION A'

THEN ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Region Price])

ELSE ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Price])

END

• 4. Re: Aggregate and Non Aggregate in IF Statements

Thanks Richard

There seems to be two issues with the these lines:

THEN ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Region Price])

ELSE ATTR([Margin Deficit])+SUM([Unit Conversion Amount Required]*[New Price])

Tableau is highlighting that [Margin Deficit] is already an aggregation and that it cannot be further aggregated.

Thanks

Chris

• 5. Re: Aggregate and Non Aggregate in IF Statements

Just take the ATTR() off them then.  As I said, I had to guess how your fields were defined.

It looks as if your original issue was just that [REGION} needed to be an aggregate in that IF statement, so:

IF [REGION] = 'REGION A'

needs to be:

IF ATTR([REGION]) = 'REGION A'

• 6. Re: Aggregate and Non Aggregate in IF Statements

Perfect - Thank you Richard. I will get there in the end

• 7. Re: Aggregate and Non Aggregate in IF Statements

Hi Richard, I have been using the ATTR function over the past couple of days and am noticing that it sometimes displays an incorrect grand total, is this normal? if so is there a way around it so that I can use the correct total amount in other calculations?

Many thanks

Chris

• 8. Re: Aggregate and Non Aggregate in IF Statements

The usual trap with ATTR() is that if you use it in a context where there is not a unique value for the field that you are trying to apply the ATTR() funtion to, it returns an undefined value (which displays as a '*') if the field is actually on display.

So possibly that's what's happening.  It's not always obvious from just looking at the view, depending where your ATTR() function is.

I'd have to see the workbook (or an example workbook that demonstrates the problem) to be able to work out what's going on, though.

• 9. Re: Aggregate and Non Aggregate in IF Statements

Here is the attached work book - As you can see the grand totals are not adding up to what they should be with 'Market Conversion Value' - ie if you copy the cross tab out to excel and total.

Hope this helps

Many thanks also for your help with this one

• 10. Re: Aggregate and Non Aggregate in IF Statements

Well I know what's happening - but I'll have to have a think about how to get what you want.

The issue is that that the Grand Total doesn't just add up all the rows - it evaluates the functions in the rows at a higher level of aggregation.

You can see that very clearly - and understand why it has to do that - on AVG([Outlay]).  The Grand Total at is returning the average Outlay over the entire record set, rather than adding up all of the individual average outlays for the rows.

Similarly the Grand Total line for [Market Conversion Value] is evaluating the expression:

SUM([Converted Units])/([New ASP]-AVG([Outlay]))

over the entire record set - i.e. the total converted units over the new ASP minus the overall average outlay.

Without understanding your data I don't know for certain - but it could even be that what Tableau is doing makes more sense.  I've had that happen - and I hate it when it does - how does it KNOW? ;-)

Let us know if you really do want the sum of the individual rows.  Mark the post "Attention: Joe Mako".  ;-)

• 11. Re: Aggregate and Non Aggregate in IF Statements

Hi Richard - I can see what you are saying, I put in a request from Tableau support and came up with this solution.

Thanks again

Chris

• 12. Re: Aggregate and Non Aggregate in IF Statements

Well it might not get 10/10 for elegance - but glad it gives you the answer you want.  ;-)

• 13. Re: Aggregate and Non Aggregate in IF Statements
1 of 1 people found this helpful
• 14. Re: Aggregate and Non Aggregate in IF Statements

Yes, I agree with Joe's comments in the other thread - his way is much more robust.

1 2 Previous Next