Implement the z-test for proportions

Use this statistical test to determine whether the difference between the proportions of two samples is significant or not.

  1. In the Field Picker, select the Count measure to include it in the Data panel.

    The following step creates a table calculation for use in subsequent table calculations. After you create this table calculation you can hide the fields for these measures in the Data panel.

  2. Create a table calculation for Count:
    1. In the Field Picker, select Add > Table Calculation.
    2. Enter ${ and then select the Count measure from the drop-down list.
    3. In the Name text box, enter Count as the field name.
    4. Click Save.
  3. Create a table calculation for CountA:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy the table calculation template into the Expression text box. 
      # Convert NULLS to 0s to enable proper calculation
if(is_null(${sample1_count}),0,${sample1_count})
    3. Replace all instances of ${sample1_count} with the ${count} table calculation.
    4. In the Name text box, enter the field name.
      For example: CountA
    5. Click Save.
  4. Create a table calculation for Column total count:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy the appropriate table calculation template into the Expression text box. 
      sum(${sample1_count})
    3. Replace ${sample1_count} with the ${counta} table calculation.
    4. In the Name text box, enter the field name.
      For example: Column Total Count
    5. Click Save.
  5. Create a table calculation for Proportion %:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy the appropriate table calculation template into the Expression text box. 
      ${counta}/${column_total_count}
    3. In the Name text box, enter the field name.
      For example: Proportion %
    4. Click Save.
  6. Create a table calculation for Period over period change (PoP%):
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy the table calculation template into the Expression text box. 
      if(${period/sample_group}=1 # 1 is equal to the period number
# This can alternatively be equal to a sample_group name
# ----- Begin second pivot section ----------------------------------
OR ${second_pivot}!=pivot_offset(${second_pivot}, -1) 
# what the previous line of code is doing is that given the current column 
# like "Canada", return true if the column before's value is not "Canada"
# ----- End second pivot section   ----------------------------------
,null,
((${proportion}-pivot_offset(${proportion}, -1)))) 
# the -1 arguments above are taking the previous value
    3. Replace ${period/sample_group} in the first line with the period, sample group or wave dimension in your data.
    4. If using the second pivot, replace all instances of ${second_pivot} with the second dimension you are pivoting on. For example, Country. Otherwise, remove the second pivot section.
    5. In the Format drop-down list, select Percent.
    6. In the Decimals text box, enter 0.
    7. In the Name text box, enter the field name.
      For example: PoP%
    8. Click Save.
  7. Create a table calculation for Row total count:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy the appropriate table calculation template into the Expression text box. 
      if(${period/sample_group}="1" # 1 is equal to the period number
# This can alternatively be equal to a sample_group name
# ----- Begin second pivot section ----------------------------------
# Remove this section if you aren't including a second pivoted field
OR ${second_pivot}!=pivot_offset(${second_pivot}, -1)
# what the previous line of code is doing is that given the current column 
# like "Canada", return true if the column before's value is not "Canada"
# ----- End second pivot section   ----------------------------------
,null,
${sample1_count}+if(is_null(pivot_offset(${sample2_count},-1)),0,pivot_offset(${sample2_count},-1)))
# the -1 arguments above are taking the previous value
    3. Replace ${period/sample_group} in the first line with the period, sample group or wave dimension in your data.
    4. If using the second pivot, replace all instances of ${second_pivot} with the second dimension you are pivoting on. For example, Country. Otherwise, remove the second pivot section.
    5. Replace all instances of ${sample1_count} and ${sample2_count} with the ${count} table calculation.
    6. In the Name text box, enter the field name.
      For example: Row Total Count
    7. Click Save.
  8. Create a table calculation for Minimum sample size:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy this table calculation template into the Expression text box: 
      # Using 95% confidence interval (1.96) and Power of 0.8 (~0.84)
if(${period/sample_group}="1" # 1 is equal to the period number
# This can alternatively be equal to a sample_group name
# ----- Begin second pivot section ----------------------------------
# Remove this section if you aren't including a second pivoted field
OR ${second_pivot}!=pivot_offset(${second_pivot}, -1)
# what the previous line of code is doing is that given the current column 
# like "Canada", return true if the column before's value is not "Canada"
# ----- End second pivot section   ----------------------------------
,null,(
(2* power((1.96+0.84),2) 
  *
  (${sample_1_proportion}*(1-${sample_1_proportion}) 
  + 
  pivot_offset(${sample_2_proportion},-1)*(1-pivot_offset(${sample_2_proportion},-1)))
  /
  power((${sample_1_proportion}-pivot_offset(${sample_2_proportion},-1)),2))))
  
  #sample_1_proportion and sample_2_proportion are the PERCENTAGES of the total count
    3. Replace ${period/sample_group} in the first line with the period, sample group or wave dimension in your data.
    4. If using the second pivot, replace all instances of ${second_pivot} with the second dimension you are pivoting on. For example, Country. Otherwise, remove the second pivot section.
    5. Replace ${sample1_proportion} and ${sample2_proportion} with the ${proportion} table calculation.
    6. In the Format drop-down list, select Decimals.
    7. In the Decimals text box, enter 0.
    8. In the Name text box, enter the field name.
      For example: Minimum Sample Size Z-Test Proportion
    9. Click Save.
  9. Create a table calculation for Z-test proportion:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy this table calculation template into the Expression text box: 
      # Using 95% confidence interval (1.96) and Power of 0.8 (~0.84)
if(${period/sample_group}="1" # 1 is equal to the period number
# This can alternatively be equal to a sample_group name
# ----- Begin second pivot section ----------------------------------
# Remove this section if you aren't including a second pivoted field
OR ${second_pivot}!=pivot_offset(${second_pivot}, -1)
# what the previous line of code is doing is that given the current column 
# like "Canada", return true if the column before's value is not "Canada"
# ----- End second pivot section   ----------------------------------
,null,

#p1-p2
(${sample_1_proportion} - pivot_offset(${sample_2_proportion},-1)) 
/ 
sqrt( 
  #p
  (${sample_1_proportion_count} + pivot_offset(${sample_2_proportion_count},-1))
    /
    (${sample_1_total_count} + pivot_offset(${sample_2_total_count},-1))
  * 
  #1 - p
  (1 - (${sample_1_proportion_count} + pivot_offset(${sample_2_proportion_count},-1))
    /
    (${sample_1_total_count} + pivot_offset(${sample_2_total_count},-1)))
    #1/n1 +1/n2
  * (1/${sample_1_total_count} + 1/pivot_offset(${sample_2_total_count},-1)) 
  )
  )
    3. Replace ${period/sample_group} in the first line with the period, sample group or wave dimension in your data.
    4. If using the second pivot, replace all instances of ${second_pivot} with the second dimension you are pivoting on. For example, Country. Otherwise, remove the second pivot section.
    5. Replace ${sample1_proportion} and ${sample2_proportion} with the ${proportion} table calculation.
    6. Replace ${sample1_proportion_count} and ${sample2_proportion_count} with the ${counta} table calculation.
    7. Replace ${sample1_total_count} and ${sample1_total_count} with the ${column_total_count} table calculation.
    8. In the Name text box, enter the field name.
      For example: Z-test Proportion
    9. Click Save.
  10. Create a table calculation for Final z-test proportion:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy this table calculation template into the Expression text box: 
      # The following if statement allows only Wave over Wave comparison between 2 different waves
if(${period/sample_group}="1" # 1 is equal to the period number
# This can alternatively be equal to a sample_group name
# ----- Begin second pivot section ----------------------------------
# Remove this section if you aren't including a second pivoted field
OR ${second_pivot}!=pivot_offset(${second_pivot}, -1)
# what the previous line of code is doing is that given the current column 
# like "Canada", return true if the column before's value is not "Canada"
# ----- End second pivot section   ----------------------------------
,null,                                                
# The following if statement requires the sample size for each sample to meet a minimum sample 
# size in order to calculate the t-test score
  if(${sample_1_proportion_count} < ${minimum_sample_size_z_test_proportion} 
    OR
    pivot_offset(${sample_2_proportion_count}, -1) < ${minimum_sample_size_z_test_proportion},null,
    # Calculate t-test score if the above if statements are true
    ${z_test_proportion}))
    3. Replace ${period/sample_group} in the first line with the period, sample group or wave dimension in your data.
    4. If using the second pivot, replace all instances of ${second_pivot} with the second dimension you are pivoting on. For example, Country. Otherwise, remove the second pivot section.
    5. Replace ${sample1_proportion_count} and ${sample2_proportion_count} with the ${counta} table calculation.
    6. In the Name text box, enter the field name.
      For example: Final Z-Test Proportion
    7. Click Save.
  11. Create a table calculation for Z-test proportion significant:
    1. In the Field Picker, select Add > Table Calculation.
    2. Copy this table calculation template into the Expression text box: 
      if(is_null(${final_z_test_proportion}),
  null,
  if(abs(${final_z_test_proportion})>=1.96,
  1,0))
      • Uses 95% confidence interval (1.96)
      • For the two-tailed hypothesis
    3. In the Name text box, enter the field name.
      For example: Z-test Proportion Significant
    4. Click Save.