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

    Calculated filed from related data source with IF statement

    John Quintana

      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!