How to count only yes values from a yesno variable

Gabi1 · 03-18-2023 12:55 AM

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.

Shru

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

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

Thanks!

Shru

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

Is that what you mean?

That is what I get:

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

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

Shru

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

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