Calculating Sum of Yes/No Dimension in a Table Calculation

[JVFrancis]

I am trying to create a table calculation/custom field by summing up a yes/no dimension but I am not getting the desired result in my data. Below is my table calculation,

Registered_Users (Custom Field):
sum(if(${users.is_registered},1,0))

It gives the same sum for all the rows in the data which is incorrect. I wrote a case statement to mimic the above table calculation in SQL runner and I was able to get the desired result. Below is my sql case statement,

sum(case when users.is_registered=‘Yes’ then 1 ELSE 0) as Registered_Users

Can someone please help? Can i create a table calculation with the similar case statement based approach?

[Dawid]

All you need to do is make a tiny change to your if statement:

sum(if(${users.is_registered} = yes, 1, 0))

Try the above

[JVFrancis]

Dawid_Nawrot:

sum(if(${users.is_registered} = yes, 1, 0))

I tried the solution but it still returns the same number for all the rows even for rows where is_registered=No

[swan]

Try using

running_total( if(${users.is_registered} = yes, 1, 0) )

This will increment a total in the column each time a row has a value of yes in the users.is_registered column

no | 0
yes | 1
no | 1
no | 1
yes | 2
no | 2
etc

[Dawid]

I’m not exactly sure what you want your output to look like, can you show us?

[ajones]

Try this calculation.
sum(if(${user.is_registered},1,0))

This will show you the the sum of all “yes” in each row of the calculation column. So if there are 50 registered users, each row in this column will show the number 50.

[JVFrancis]

I created a Custom Dimension first using if(${users.is_registered} = yes, 1, 0) and then created a Custom Measure by doing a sum on if(${users.is_registered} = yes, 1, 0) and it worked.