2 Replies Latest reply on Jun 9, 2016 11:40 AM by Brandi Barnes

    Error: Mix of aggregate and non-aggregate in an IF statement

    Brandi Barnes

      Hi all,

       

      I'm working on an eCommerce dashboard. The logic I need to perform is: How many customers have placed at least 10% of their orders on the eCommerce system (vs faxing)?

       

      I have an aggregate function called Online Orders % that does the following :

       

      COUNTD(IF [Channel Int] = 1 THEN [Order #] END) / COUNTD([Order #])

       

      Note: If Channel Int = 1 it signifies an order that was placed online.

       

      Now, what I was wanting to perform is this:

       

      COUNTD(IF [% Online Orders] >= 0.1 THEN [Client ID] END)

       

      But I am getting the error for mixing aggregate and non-aggregate in an IF statement.

       

      Any help is appreciated.

        • 1. Re: Error: Mix of aggregate and non-aggregate in an IF statement
          pooja.gandhi

          Hi Brandi!

           

          Pretty difficult to say without looking at your sample data. However, I am assuming because your online % orders is already an aggregate you could try:

           

          IF [% Online Orders] >= 0.1 THEN countd([Client ID]) END

           

          Otherwise, I recommend attaching atleast a few rows of sample data in an excel file or a tableau packaged workbook.

          • 2. Re: Error: Mix of aggregate and non-aggregate in an IF statement
            Brandi Barnes

            Pooja, the reason that won't work is because it will count all the clients if the overall order rate is over 10%, which it is, so it just counts all the clients.

             

            The purpose for this to calculate retention. We want to know how many customers consistently place 10% each week. For example, in week 1, if 2 customers placed 10% online and then in week 1 only 1 of those placed 10% online, then we would consider that 50% retained.

             

            The function over time will be (# of customers who made the adoption requirement this week - # new customers) / # customers who made the adoption requirement last week.

             

            To figure out how many were adopted in a particular week I need to count the customers based on online order rate.

             

            I've attached a sample workbook.