4 Replies Latest reply on May 9, 2018 5:48 PM by Shinichiro Murakami

    IF Statement, Cannot mix aggregates and non-aggregates...

    Elie Rahi

      I get this error all the time and sometimes understand why and other times I don't. I have the following calc field that I want to apply row-by-row;

       

      IF [Duration] >= 1.67 THEN [Static Data].[Cinema tickets]

      ELSEIF [Duration] < 1.67 THEN [Duration]/1.67*[Static Data].[Cinema tickets]

      ELSE 0

      END

       

      This came up with the error so then I was advised to put "sums" on all the fields. But I don't get why if it's a row-by-row calculation. It works fine in Excel so why not Tableau.

       

      IF SUM([Duration]) >= 1.67 THEN SUM([Static Data].[Cinema tickets])

      ELSEIF SUM([Duration]) < 1.67 THEN SUM([Duration])/1.67*SUM([Static Data].[Cinema tickets])

      ELSE 0

      END

       

      But then I want to apply the formula based on [Person] field:

       

      IF [Person] = "Patient" OR [Person] = "Sibling" OR [Person] = "Friend" OR [Person] = "Cousin" THEN

       

      (IF SUM([Duration]) >= 1.67 THEN SUM([Static Data].[Cinema tickets])

      ELSEIF SUM([Duration]) < 1.67 THEN SUM([Duration])/1.67*SUM([Static Data].[Cinema tickets])

      ELSE 0

      END)

       

      ELSE 0

      END

       

      And I get the error again. Can someone please tell why this is wrong and how to fix this?