6 Replies Latest reply on May 1, 2018 7:00 AM by John Quintana

# Calculated filed from related data source with IF statement

Hello!

I have two related data sources, they are joined on the fields "Account Executive Name" and "Market".

I am trying to create a calculated fields that provides the net amount of revenue from the secondary data source for both "new business" and for "existing business".

The logic  I need to use is as follows:

SUM(IF [Stage Name]="Closed-Booked" and [Type]="New business" then [This Year Net])

"This Year Net" is a calculated filed from the secondary data source:

IF ABS(datediff('month', [Effective Date], [Today])) <= 3

THEN

ZN([Change Amount])*(1-ZN([Agency Commission]))

ELSE

0

END

When I try to use this formula: SUM(IF [Stage Name]="Closed - Booked" and [Type] = "Existing Business" THEN [Pacing].[This Year (Net)] END)

I get an error that says, "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources"

What is the best way to go about getting that logic applied to bring in the calculated field from the related data source?

Thank you!

• ###### 1. Re: Calculated filed from related data source with IF statement

Hello Jhon,

an IF function can not compare groupings in tableau. your [This Year Net] field becomes aggregation because it is a relational data source ...

you could share the columns in your data source I think you could fix it from the data source without having to make a relationship in the worksheets .

• ###### 2. Re: Calculated filed from related data source with IF statement

Thank you Pablo,

I am not sure exactly what you mean by sharing the columns in the data source. Can you help me understand that a little better?

Thank you

• ###### 3. Re: Calculated filed from related data source with IF statement

Sry John, I was at lunch...

I understand that you relationship between sources of information is with the fields "Account Executive Name" and "Market"... My proposal is to have a single source of data and that you do not have to do the relationship within the spreadsheet....

Actual:

Expectation: (Only One)

in this way your field "[This Year Net]" would not be an aggregation and you could call it in the IF funtion.

• ###### 4. Re: Calculated filed from related data source with IF statement

Unfortunately I can not create one data source out of these, unless you are referring to blending the data with tableau?

• ###### 5. Re: Calculated filed from related data source with IF statement

yes, my recomendation is that you have the relationship in the blending...

If you have a file with excerpts as an example and share it with me, I could edit it and show you how to do the relationship

• ###### 6. Re: Calculated filed from related data source with IF statement

I understand what you are saying, I am not sure that I am able to do that.

I tried this: (IF ATTR([Stage Name])="Closed - Booked" and ATTR([Type]) = "Existing Business" THEN SUM([Pacing].[This Year (Net)]) END)

but when I do that, it does not return a "This Year" value.

Any thoughts on this route?