Solved

Creating table calculation across multiple measures

  • 10 November 2020
  • 3 replies
  • 92 views

I am looking to add a column that calculates lift/variance across testing groups. I have a bunch of measures I have created but how do I then create the variance column on top of all measures for test group A v. all measures for test group B?

 

The below is a hypothetical example and is after I have transposed the results so really it is a row for control with many columns and a row for Exp A. Essentially I want to add a row or column that is (ExpA - Control) / Control. So I have the lift across all the measures I have created.

 

 

icon

Best answer by Naomi_Johnson 11 November 2020, 11:48

Hi @jessie1990 

In order to have the difference/variance etc as a new row, you can use a derived table and union the row containing the differences/variances.

I have created an example below using Status and two measures from Order Items.

By using union to combine the values by Status and comparisons between cancelled and processing, the difference is another row in the table.

 

In order to be able filter the table by user_id, I used a templated filter

filter: user_id {
type: number
}

Multiple filters can be added this way, and suggestions to the filters can be added using suggest_dimension

 

The results in the Explore are below:

 

I used the row_number() operator in the derived table to order the rows, so the differences can be the last row of the results.

I’ve used this dimension to order the rows and then hidden it from the results.

 

Let me know if you have any questions, and I hope this is a fix for you.

Naomi, Redkite

View original

3 replies

Userlevel 1

Hi @jessie1990,

We can reference individual values on a per line item level in a table calculation which will create a new column in our explore. For example, if I want to do the computation of (Count 2 - Count 1)/Count 2. I can create a table calculation to do this. For the California line item that will be (1173 - 1175)/1173

 

 

We do have built in functions for variance var_pop and var_samp which we can access in table calculations. If we wanted to aggregate a row, we can do that with a function that would aggregated. We can get the Standard Deviation, average, sum, or variance of the entire column. Those would look a bit like this in my example from above. Any of these could be used to create a new aggregate row in the explore. 
 

sum(${users.count1})
stddev_pop(${users.count2})
mean(${users.count2})
var_pop(${users.count1})

 

I hope this helps. Please let me know if you have any questions. 

 

Thanks,

Eric

I think my issue is that my test variant is based on a pivot so I can’t figure out how to reference the Control or Exp A columns as that is based on a value of test_variant. So it would be more like I was wanting to compare California to Texas across many metrics ie user count 1 and user count 2 - but instead of having to make 20 individual measures I just want to make one that just compares everything from California to everything from Texas.

 

So my data really looks like this before I transpose it. The rows are the test variants and then I have multiple columns that are measures that I have created. So I want to add a 4th rows that compares ExpA to Control?

 

Userlevel 1

Hi @jessie1990 

In order to have the difference/variance etc as a new row, you can use a derived table and union the row containing the differences/variances.

I have created an example below using Status and two measures from Order Items.

By using union to combine the values by Status and comparisons between cancelled and processing, the difference is another row in the table.

 

In order to be able filter the table by user_id, I used a templated filter

filter: user_id {
type: number
}

Multiple filters can be added this way, and suggestions to the filters can be added using suggest_dimension

 

The results in the Explore are below:

 

I used the row_number() operator in the derived table to order the rows, so the differences can be the last row of the results.

I’ve used this dimension to order the rows and then hidden it from the results.

 

Let me know if you have any questions, and I hope this is a fix for you.

Naomi, Redkite

Reply