If statement Calculation that includes nulls and checks for prefix

Viewed 19

This is for tableau calculated field, but the code is common for any excel or literally anything. Please help its just a simple if statement but with multiple conditions.

I am trying to count a distinct number of customers with a few conditions

  1. customers the with 'ABC' prefix and repeat order = no should be included in this metric
  2. Two customer numbers should be disregarded from the total count
    • all other customers (regardless of prefix or repeat order selection)
  3. Delivered date should not be null

Below is my calculation

COUNTD(
    IF NOT ISNULL([Delivered Date]) 
    AND (
            (STARTSWITH([Customer ID],"ABC") AND [Repeat order]="No")
                 OR NOT STARTSWITH([Customer ID],"ABC") )
    AND NOT ([Customer ID] IN ("ABC-AA2517","ABC-CO1325"))
    THEN 
        [Customer ID] 
    END
)

Numbers

-Overall, I have 11282 customers with a date.
---2239 customers have ABC prefixes. 
---Among which 1577 is yes for repeat order and 662 is no for the repeat order.  

So the total number for this metric should be 11282-1577-the 2 disregarded customer numbers. The final number should be 9703, but it shows 8599 as the total.

I think its not include those 660 customers. Not sure what mistake I am making in the calculation.

0 Answers
Related