17 Replies Latest reply on Jul 2, 2018 7:24 PM by Dave Attrill

# cannot mix aggregate and non-aggregate calculation

Hello everyone, I am fairly new to Tableau and trying to work through a few issues I am coming up against.  I have managed to get one of my calculations to work by conditioning the aggregate but the calculation I am having issues with now has two SUMs in it and I think that's where I am running into problems now.  If I try to condition the aggregate by wrapping it in a sum I don't get proper results so I must be missing something.  Is there some other way to format the calculation below?

IF ([Post Date] >= [StartDate]) AND ([Post Date] <= [EndDate]) THEN
(sum(([Price1]-[Cost Ns])*[Qty Ship]))/(sum([EXT]))
ELSE
0
END

• ###### 1. Re: cannot mix aggregate and non-aggregate calculation

Wrap the dimension fields in ATTR()

ATTR([Post Date])

(You don't need to do it with parameters.  Just dimensions.)

You have an aggregate in the calc (  SUM()  )  so everything in there needs to have an aggregate designation of some sort.  ATTR() takes care of that.

• ###### 2. Re: cannot mix aggregate and non-aggregate calculation

I just gave that a try and the calculation is valid but its returning all zeros, calculation for Margin% as it currently sits is below

IF ATTR([Post Date] >= [StartDate]) AND ATTR([Post Date] <= [EndDate]) THEN

(sum(([Price1]-[Cost Ns])*[Qty Ship]))/(sum([EXT]))

ELSE

0

END

My calculation for Margin\$ listed below works great, but for some reason the Margin% is not

sum (IF ([Post Date] >= [StartDate]) AND ([Post Date] <= [EndDate]) THEN

(([Price1]-[Cost Ns])*[Qty Ship])

ELSE 0

END)

• ###### 3. Re: cannot mix aggregate and non-aggregate calculation

At this point I'll need a sample workbook to look at.

• ###### 4. Re: cannot mix aggregate and non-aggregate calculation

I have uploaded an example of what we are trying to do, you should be able to see that the YTD Margin % column is 0 which is not right

• ###### 5. Re: cannot mix aggregate and non-aggregate calculation

First of all, to wrap the dimensions in ATTR(), you want to do this:

IF ATTR([Post Date]) >= [StartDate] AND ATTR([Post Date])<= [EndDate] THEN

not this:

IF ATTR([Post Date] >= [StartDate]) AND ATTR([Post Date] <= [EndDate]) THEN

But that's not the actual problem here.

Your calc expects [Post date] to be on the sheet.  And it's not.

I made a copy of your sheet and took everything else off but [Gross Margin %] so I could see what's going on.  See the copy in the attached.  I put stuff on the text shelf for the sake of debugging.  I put each component of the gross margin calc on text so I could see the values.  (Note:  I made Calc 2 just for my own purposes.  Sometimes SUM([A]-[B]) is not always the same as SUM([A])-SUM([B]).  It is here though.  So you can ignore this calc.)

Because the date field isn't on the sheet, Tableau is just returning nulls for that part of the equation in the calc.  To get around that, made [Price1 in range] as a row-level calc so that tableau does the evaluation at the record level and not at the aggregate level.  (Rows outside the range get NULL for the value.)  Now when I put SUM([Price1 in range]) on the sheet, I get the value of that measure for the rows in that range.  Do the same with the other measures.  And then you don't even have to look at the date field in the gross margin calc because it's already weeded out by the row-level measure calcs.

• ###### 6. Re: cannot mix aggregate and non-aggregate calculation

ok, Ive managed to get thing working as we talked about but now am trying to filter irrelevant rows, taking a look at the updated attachment I am trying to filter out rows where the 'Actual YTD Margin %' is 0% AND if thee 'Budgeted Margin %' is null.  I think the way to do this would be to create a true or false but I cant get it right.

• ###### 7. Re: cannot mix aggregate and non-aggregate calculation

I don't see and attachment.  The one on the original post is still the old workbook.

• ###### 8. Re: cannot mix aggregate and non-aggregate calculation

Correct way of doing this:

SUM(

IF ([Post Date] >= [StartDate]) AND ([Post Date] <= [EndDate]) THEN

(([Price1]-[Cost Ns])*[Qty Ship])

ELSE

0

END

)

/

SUM(

IF ([Post Date] >= [StartDate]) AND ([Post Date] <= [EndDate]) THEN

[EXT]

ELSE

0

END

)

• ###### 9. Re: cannot mix aggregate and non-aggregate calculation

Attachment is now posted which shows a few rows of data where the 'Actual YTD Margin %' is 0% AND if thee 'Budgeted Margin %' is null which I need to find a way of filtering out.

• ###### 10. Re: cannot mix aggregate and non-aggregate calculation

Here is what I see in the attachment:

All [Actual YTD Margin %] are zero.  But with respect to filtering, are you looking to filter out all rows that are like the one with red circles?  What about the one with blue circles?

And when we filter these, do you just want those rows hidden?  Or do you also want them taken out of any totaling in the "Total" lines?  (Once I start messing with this, these last two questions might be moot.  They may end up being one and the same.  I'm not sure yet.)

• ###### 11. Re: cannot mix aggregate and non-aggregate calculation

Pretty well the situation is when the 'Actual YTD Margin %' and 'Budgeted Margin %' are both null, or

'Actual YTD Margin %' is 0% and 'Budgeted Margin %' is null then

removed the row from the vis as well as not be added in the total calculations at the bottom.

• ###### 12. Re: cannot mix aggregate and non-aggregate calculation

Where did your last workbook go?  It's not attached any more.  (PS:  Give it a different name so we can keep them straight.  Tack on "A" at the end of the name or something.)

• ###### 13. Re: cannot mix aggregate and non-aggregate calculation

Sorry, should be there now.

• ###### 14. Re: cannot mix aggregate and non-aggregate calculation

I think I managed to get it to work..  by creating this calc and filtering against it

IF isnull(ATTR([Budget Margin %]))

AND isnull([Gross Margin %])

THEN 'False'

ELSE 'True'

END