6 Replies Latest reply on Jun 25, 2015 11:32 AM by minh.nguyen.3

# How can I calculate statistical significance versus control groups for pairs of cells?

Esteemed Community,

I am migrating my recurring direct marketing campaign reporting to Tableau, which will surely introduce many improvements to the process.  However, I'm stuck on calculations involving pairs of cells to calculate statistical significance.  Here is how my table looks with the desired calculation superimposed:

So you see there are pairs of cells, each with a Mail and Control cell.  Essentially I want this Significance calculation shown below to be done for each pair where the letter codes are the same:

ABS((Mail Resp Rate – Ctrl Resp Rate) /

(SQRT(((Mail Responses +  Ctrl Responses) /

(# Mail + # Control)) *

(1-((Mail Responses + Ctrl Responses)/

(# Mail + # Control))) *

(# Mail + # Control)/

(# Mail * # Control))))

These calculations would reset with each change in value of the Pair variable.  Notice I have it assigned to the Mail cell.  For extra credit, this needs to be done for the total, too.

Any ideas how I’d program that?

Thanks for any assistance!

Brent

(v6.1.3)

• ###### 1. Re: How can I calculate statistical significance versus control groups for pairs of cells?

To simplify this challenge, I just need to know how to make Tableau’s calculation logic be like:  Do this calculation where the value of the Pair variable for a mail cell equals the value of the Pair variable for the control cell.

• ###### 2. Re: How can I calculate statistical significance versus control groups for pairs of cells?

I think I'm dead in the water.  The calculation involves aggregated data.

• ###### 3. Re: How can I calculate statistical significance versus control groups for pairs of cells?

Can you please provide the source data that you would connect Tableau to, and an Excel file with the formulas that you want to recreate in Tableau?

• ###### 4. Re: How can I calculate statistical significance versus control groups for pairs of cells?

Joe,

Thanks for your willingness to help.  I have attached the Excel file with the formulas.  I truly wish I could provide the Tableau data, but it's confidential financial data.  I've struggled to disguise it but it's just too complex.  I have 6 tables joined together to create the dataset, and the linking fields are the ID numbers that I can neither disclose nor can I change their values without losing the connection.  I know it's asking too much to have you answer without being able to play around with the data.  Thanks all the same.

• ###### 5. Re: How can I calculate statistical significance versus control groups for pairs of cells?

Brent,

I would like to help you, but you are currently not enabling that.

Please send me an email, so we can arrange for a webex and hopefully we can find a solution.

• ###### 6. Re: How can I calculate statistical significance versus control groups for pairs of cells?

Hi Joe,

Was this question ever resolved? Did you get a chance to work with Brent via WebEx? I am also interested in adding this type of data into a dashboard for marketing campaign analysis.

Thanks!

Minh