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

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

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

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.