How to count only yes values from a yesno variable

Gabi1
Participant I

Hi, 

I am a total newbie and I have one very basic question.

I need to calculate the rate of people that made a purchase. There is a variable for this with yes/no values. 

I need to divide only the count of “yes” of the latter variable by the total number of visitors (which I have).

The question is, how can I get the count of “Yes” only from the purchase variable?

What would be sort of an alternative of countif in Looker? I’ve tried conditional sum, but it does not work.

0 6 931
6 REPLIES 6

Shru
Participant III

One option would be to create a separate measure for purchase variable. Then pass a filter ‘Yes’ in that same measure by referencing your ‘yesno’ dimension. This will get the count of only the ‘Yes’ ones. 

It’s works like a having clause for your aggregate measure.

You may then proceed to call the measure which gets the total value in the look, and also call the new measure with Yes condition. Use a table calculation feature to calc the final rate.

Gabi1
Participant I
ff756682-19b9-44c9-839d-d3809d97a84d.png

I tried this before, but it does not work. What am I doing wrong?

Thanks!

Shru
Participant III

Select the dimension which you want the ‘count of ‘ under ‘field of measure’ then pass condition as Yes.

Eg > count of Apples having color = Red. 

Here Apple is the ‘Measure’ and Red is ‘Yes’.

Also, instead of Custom filter try the above ‘Filter name’ option from the snap. Call the YesNo dimension here, pass ‘Yes’ under filter value. Mostly it should by default bring YesNo under filter value options to select.

Gabi1
Participant I

Is that what you mean?

c9feb54e-a16a-4e4c-ba6b-ea5e9c40b0e6.png

That is what I get:

9ad2b447-772f-44ee-a9f3-f88aa140a133.png

even though there are 3000 values that are “Yes”.

I don’t understand what I do incorrectly here..

Shru
Participant III
510f7386-8b3d-4a0f-b7dc-1df56cec98e7.png
Sample

Maybe your total measure field looks like the 1st column. You need to make similar measure for the 2nd column and pass the filter conditions as Yes. Check which dimension was called for the Total count, you will need to call the same one under ‘Field to measure’.

‘Is Converted(Yes/No)’ is not the correct dimension to use as count but only as filter. 

Gabi1
Participant I

Thanks Shru. Sounds about right! will try in a sec