1 Reply Latest reply on Sep 28, 2016 3:10 PM by Stoyko Kostov

    If and then functions

    Vinh Phan

      Hello everybody,

      I am new here and I currently have a problem with IF ... Then Function:

      I write a formula and its doesn't work:

      If CountD(Customer)>=80 AND Year([Order Date]) = [Year Parameter] Then SUM([Sales])/2 END
      

      Please tell me how to get it done.

      Thank you.

        • 1. Re: If and then functions
          Stoyko Kostov

          Hello Vinh,

           

          You are mixing aggregate and non-aggregate functions.

           

          I can make some suggestions on how to fix this, but it would be best if I knew what you were trying to do.

           

          One possibility is to first add a filter on [Order Date], and then leave your IF statement as If CountD(Customer)>=80 Then SUM([Sales])/2 END.

           

          Another possibility is to introduce an aggregation on the date as well: If CountD(Customer)>=80 AND Year(MAX([Order Date])) = [Year Parameter] Then SUM([Sales])/2 END

           

          Clearly the 2 approaches will give you 2 different results - try them out and see what suits your needs.

           

          You can read this KB article for more insight: Resolving "Cannot mix aggregate and non-aggregate arguments" Calculation Error | Tableau Software

           

          Let me know if you have more questions - I'd be glad to help. If you provide sample data and/or workbook, and state what you are trying to achieve, that would even be better.

          1 of 1 people found this helpful