11 Replies Latest reply on Sep 9, 2016 9:41 AM by David Li

# Error in calculation field??

Hi All,

I have a field called [B/A] = After,Before,No. I have created a field called score as:

1.Score:

(count([Field1])/(Count([Field1])+Count([Field2])+Count([Field3])))-(count([Field3])/(Count([Field1])+Count([Field2])+Count([Field3])))

I got the correct answer.

I am creating one field as below:

2. BScore :

If [B/A]='Before' then

(count([Field1])/(Count([Field1])+Count([Field2])+Count([Field3])))-(count([Field3])/(Count([Field1])+Count([Field2])+Count([Field3])))

END

the above 2nd point gives the error as :

"Cannot mix aggregate and Non aggregate comparisons or results in  'IF' expressions"

So, I have used fixed option as below

if [B/A]='Before' then

{fixed [Ooid] :((count([Field1])/(Count([Field1])+Count([Field2])+Count([Field3])))-(count([Field3])/(Count([Field1])+Count([Field2])+Count([Field3]))))}

END

But it is giving wrong value when I use the fixed option.

Can someone help me with the correct code for the second point?

Best wishes,

Sandeep

• ###### 1. Re: Error in calculation field??

Hi Sandeep, is the field [B/A] a Dimension?

• ###### 2. Re: Error in calculation field??

Hi David,

[B/A]  is a dimension consists of 3 levels: After, Before and No.

• ###### 3. Re: Error in calculation field??

This won't work because the IF statement here works at the row level, while the calculation is an aggregate. One thing you can try is replacing that first line with this:

If ATTR([B/A]) = 'Before'

However, this will only work if your level of detail separates different keys in [B/A]. That is, if you have a cell whose records have more than one type of [B/A], this won't work.

• ###### 4. Re: Error in calculation field??

If I use If ATTR([B/A]) = 'Before' which means it will have only one level, right?

I am sorry, I did not understand the below.

" That is, if you have a cell whose records have more than one type of [B/A], this won't work."

I just used it in the formula , it gave blank.

Please let me know how can I get my output.

• ###### 5. Re: Error in calculation field??

Can you upload a packaged workbook, Sandeep? That would make this much easier to solve.

With regard to what I said before, here's an easy example: if [B/A] is in the Rows or Columns Shelf, then the calculation will work. If it's not, and there are multiple types of [B/A] in each cell, then it won't work.

• ###### 6. Re: Error in calculation field??

Hi David,

I have attached the workbook and the excel file.

Could you please help me with the attr() formula?

Best Wishes,

Sandeep

• ###### 7. Re: Error in calculation field??
2 of 2 people found this helpful
• ###### 8. Re: Error in calculation field??

Hi David,

Yes,it worked me.Is it possible to display Before and After values in the same sheet?

I created the same formula for [B/A}='After'. Now I want to display as shown below .Is this possible?

 Before 0.12 After 0.17

Thank you very much for all your help.

Best Wishes,

Sandeep

• ###### 9. Re: Error in calculation field??

Yes, that's possible, but let me clarify something: does the calculation change depending on whether [B/A] is Before, After, or No?

If the calculation does not change, then you can just remove the IF...ELSE part of the calculation entirely and let the dimensions do the filtering.

If the calculation does change, then add ELSEIF and ELSE parts to the IF...ELSE part of the calculation, like this:

If ATTR([B/A])='Before' then

(count([Field1])/(Count([Field1])+Count([Field2])+Count([Field3])))-(count([Field3])/(Count([Field1])+Count([Field2])+Count([Field3])))

ELSEIF ATTR([B/A]) = 'After' THEN

SOME OTHER CALCULATION

ELSE

SOME OTHER OTHER CALCULATION

END

1 of 1 people found this helpful
• ###### 10. Re: Error in calculation field??

Hi David,

I do not want that dimension to be in the filter. I would like to show it in the sheet itself.If there is some possibility, please do share it. Otherwise ,we can leave here.

I have other option is that we have to maintain different sheets for "before" and "after" levels.Your codes are very much useful or me.

I am happy to be a part of tableau community:)

Thank you very much for all your help and codes.

Best Wishes,

Sandeep

• ###### 11. Re: Error in calculation field??

I'm sorry, but I'm not completely sure what the desired outcome is. But one thing you could try is to change your LOD calculation (in the original post) to this:

{ FIXED [Ooid] : IIF([B/A] = 'Before', ((count([Field1])/(Count([Field1])+Count([Field2])+Count([Field3])))-(count([Field3])/(Count([Field1])+Count([Field2])+Count([Field3])))), NULL) }

And you're very welcome for the help! It's my pleasure. And welcome to the community!