0

# Count distinct in formula column

HI, I have logic in formula like this:

```distinct(
if(
not(
),
concat(
exer.orders.product_id,
exer.orders.comp_id
),
''
)

) - distinct(
if(
exer.orders.type = 'return',
concat(
exer.orders.product_id,
exer.orders.comp_id
),
''
)
)```

It will correct in case has both of type 'buy' and 'return', but in case has only one type is 'buy' or 'return' then it will be wrong because ('') in if() return. So what is correct way can i do ?, thanks.

3replies Oldest first
• Oldest first
• Popular
• Nopu,

As you pointed out distinct count is wrong because of  ('')  in the if statement.

You need to take care of this.

One way I can think of doing this is using following formula . I am sure there may be a better way of doing this.

```distinct(
if(
not(
),
concat(
exer.orders.product_id,
exer.orders.comp_id
),
'N'))
-
max(
if(
not( exer.orders.type = 'buy' ), 0,1)
)```

in place of '' I will recommend to use 'N'.

Like 1
• Nopu Nguyen
• Human
• Nopu_Nguyen
• 2 wk ago
• Reported - view

Anurag Malik In this case, how could I resolve that ?  we will get 'N' twice

```distinct(
if(
concat(
exer.orders.product_id,
exer.orders.comp_id
),
'N'
)

) - distinct(
if(
exer.orders.type = 'return',
concat(
exer.orders.product_id,
exer.orders.comp_id
),
'N'
)
)```
Like
• Nopu Nguyen
following will return 1 if 'N' is counted and 0 if 'N' is not counted using distinct function.

```max(if(exer.orders.type = 'buy' , 0,1))
```
`max(if( exer.orders.type = 'return' , 0,1))`
Like
Like Follow