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
- customers the with 'ABC' prefix and repeat order = no should be included in this metric
- Two customer numbers should be disregarded from the total count
-
- all other customers (regardless of prefix or repeat order selection)
- 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.