Count (Field) if (field = XX)

[Leooo]

Hello,

I'd like to count a field based on another field.

For example, I've created a field to count my user based on their "create date". ( COUNT(create date))

I'd like to do a second field for those same users based on the "is user" field with the value "true". I don't want to create. filter because I need this field to create a formula later on. For me, it should be a combination of "count(Create Date) and. IF(is User = true) but the following doesn't work:: 

COUNT(Create Date IF(is user=true))

I've also tried to only count the "COUNT(is user="true")", but it doesn't work. it returns 1 where it should be 1'110.

[Shru]

Hey,

Try creating a custom measure for the user field. Select measure type as count distinct. This will ensure it counts users only once. There is an option below to add additional filter where you can provide the condition of is user=true. 

[Leooo]

Thanks but it is not what I need 😕

I know how to do this, but I need to create a field with a formula so I can use it for other calculations. The big picture is to have a conversion rate for this data so I need to calculate it based on a raw number. Filtering an existing data without being able to save it doesn't help me 😕

[dsimeonova]

Hey @Leooo !
Did you consider to create this dimension and then the measure:
(i've used one of my test demo projects to build very similar thing but the idea is having the created date that is not null AND the True value for the is_user dimension that I guess you have). Assign a 1 for those cases and afterwards just to the sum the total 1's. 

  dimension: test_user_count {
    type: number
    sql: CASE WHEN ${created_date} is not null and ${users.traffic_source}='Email' then 1
              else 0
              end;;
  }
  
  measure: test_user_count_total {
    type: sum
    sql: ${test_user_count} ;;
  }

 Please share if this helped you and if not please share your thoughts to give you better example 🙂