4 Replies Latest reply on Dec 1, 2018 5:12 AM by Gavin Wong

# Counting the number of unique order id if condition is true

Hi Community,

using the sample superstore as example, i wrote 2 calculated fields

Calculation 1

[Order Date]={MAX([Order Date])}

IF [Calculation1]=TRUE then COUNTD([Order ID])

END

But the error says i cannot mix aggregate and non aggregate comparisons...

what am i missing here.

however, if i were to write

IF [Calculation1]=TRUE then [sales]

which calculates the sum of sales when the condition is met.

how then can i count the number of unique order ids when the condition is met ?

thanks

• ###### 1. Re: Counting the number of unique order id if condition is true

Are you trying to find the number of orders placed on max/latest date in your dataset ?

• ###### 2. Re: Counting the number of unique order id if condition is true

good morning

since Countd is an aggregating factor you need to aggregate the T/F statement

see below

then this (Note I used max because T is > F

you could als use attr()

will produce this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Counting the number of unique order id if condition is true

Hi, Gavin

To solve the error, you just need move the if condition inside the count() function as shown below

COUNTD(IF [Calculation1]=TRUE then [Order ID] END)

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Counting the number of unique order id if condition is true

If i count it manually, its 4.

Gavin