9 Replies Latest reply on Nov 29, 2016 10:36 AM by Sherzodbek Ibragimov

# Resolving Aggregate vs Non Aggregate Errors

Hi - I would really appreciate some advice on how best to resolve the error that occurs when you try to combine aggregated and non aggregated fields in Tableau .

Here is my specific problem--

IF {FIXED [ClinicName]: max([CreateDate])}= [CreateDate] then [percentcontrol2] END

In this case, precentcontrol2 is aggregated , the rest is not. As a result the calculation does not work.

Noah

• ###### 1. Re: Resolving Aggregate vs Non Aggregate Errors

Noah

Have you tried this formula:

IF ATTR({FIXED [ClinicName]: max([CreateDate])} =  [CreateDate]) then [percentcontrol2] END

or

IF ATTR({FIXED [ClinicName]: max([CreateDate])}) = ATTR([CreateDate]) then [percentcontrol2] END

• ###### 2. Re: Resolving Aggregate vs Non Aggregate Errors

Thanks , that second option worked.

• ###### 3. Re: Resolving Aggregate vs Non Aggregate Errors

Noah,

I am glad it helped you. In  future, any time when you use aggregate with non aggregate, make sure to use ATTR in front of non aggregate dimension, like Date, Customer Name and so on. Thank you

• ###### 4. Re: Resolving Aggregate vs Non Aggregate Errors

Great, thanks for that tip. I really appreciate your fast response.

best, Noah

• ###### 5. Re: Resolving Aggregate vs Non Aggregate Errors

Sherzodbek, one more question. I am assuming that once I have the new field (using the syntax below) created that pulls the most recent percent for each clinic that I should then be able to sort the small multiples graphs for each clinic by this field, is that correct?

When I try I don't see the new field appearing in the list of fields that I can order by. Any idea why that might be?

many thanks, Noah

IF ATTR({FIXED [ClinicName]: max([CreateDate])}) = ATTR([CreateDate]) then [percentcontrol2] END

• ###### 6. Re: Resolving Aggregate vs Non Aggregate Errors

Hi again Sherzodbek, I have a follow up question. When I create this calculation ( I call it "TopRate"  and then try to sort my small multiples tables using TopRate I notice that TopRate does not appear in the drop down list of fields to choose from . Any idea why or what a fix would be?

Noah

• ###### 7. Re: Resolving Aggregate vs Non Aggregate Errors

Noah,

I am so sorry I didn't reply for your previous request. I am not following you what you are trying to achieve, would be able to post your sample reflecting you calculations and structure so I try fix it for you? Thank you

Sherzod

• ###### 8. Re: Resolving Aggregate vs Non Aggregate Errors

Hi again Sherzod,

I think the problem I am experiencing is that the calculation I am using is creating nulls. When I try to use the calculation  to sort with it doesn't work. Do you have any recommendations for how to remove the nulls. Here's the calculation. Thanks in advance, Noah

count(if [Numerator]=1 then 1 end )/ count (if [Denominator]=1 then 1  end)

• ###### 9. Re: Resolving Aggregate vs Non Aggregate Errors

noah

you can try to utilize ZN() that converts nulls to zeros also you can try IF ISNULL(numerator) then 0 else numerator End.