0

Count distinct in formula column

HI, I have logic in formula like this:

distinct(
    if(
        not(
            exer.orders.type = 'buy'
        ),
        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
  • Newest first
  • Active threads
  • 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(
                exer.orders.type = 'buy'
            ),
            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(
              exer.orders.type = 'buy',
              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 1
Like Follow
  • Status Answered
  • 2 wk agoLast active
  • 3Replies
  • 12Views
  • 2 Following

Product Announcement


We are happy to
announce Incorta 4.8 !!!