1 2 Previous Next 15 Replies Latest reply on Aug 20, 2012 10:53 AM by Scott Tennican

# Using table calculations to calculate chi square

I am trying to incorporate some statistical analysis into our reporting so that the same reports do not have to be generated twice, once in Tableau and again using statistical software.The majority of the stats that we would like to do are based on categorical analysis so I need to use Chi Square. I know that there are no inbuilt functions within Tableau to do this. I wondered if anyone was doing this using table calculations and whether they had any code that they would be willing to share or whether they had come across any obstacles to getting this to work properly?

Thanks

• ###### 1. Re: Using table calculations to calculate chi square

Since you didn't specify which chi-squared test, I'll assume you meant the simplest Pearson chi-squared test for independence with a single categorical variable (or goodness of fit to a uniform distribution). For example, if you use Superstore data, drop Product Category on Columns and CNT(Sales) on Text to get a single row contingency table.

Then create this table calc to compute the chi-squared test statistic:

WINDOW_SUM(

SQUARE( COUNT([Sales]) -

WINDOW_SUM(COUNT([Sales])) * 1/(WINDOW_SUM(1)) )

/

( WINDOW_SUM(COUNT([Sales])) * 1/(WINDOW_SUM(1)) )

)

Tableau does NOT provide the chi-squared distribution which you compare to your test statistic to determine the p-value for you test.

1 of 1 people found this helpful
• ###### 2. Re: Using table calculations to calculate chi square

Hi Scott,

Thanks for your advice, but this wasn't quite the chi-squared statistic we were hoping to calculate.

As an example, we have a range of degree classifications - pass, merit, distinction etc - and we have the gender of the students. What we want to establish is if the gender of the student is independent of their degree awarded or if, for example, you are female you are more likely to get a distinction than if you are male. This would thus be the Chi-square as a test of independence.

Thanks

Katy

• ###### 3. Re: Using table calculations to calculate chi square

Hi Katy,

So, you'd like a Pearson's chi-squared test of homogeneity of multiple categorical population rather than just the single categorical population. I kind of suspected you might but the single pop test was quicker to code. More complete would be the goodness of fit test with multiple pops. But, that requires the ability to enter the expected proportion parameters which is even more complicated. Clearly, it would be nicer if we just built this test into the product. But, there are so many competing priorities. I'll put some thought into this as soon as possible and get back to you.

Scott

• ###### 4. Re: Using table calculations to calculate chi square

Hi, Scott:

I am definitely looking forward to this function too. It will be great if Tableau can bring in this basic statistical analysis- that will be truly one-stop shop. It is very troublesome to use additional stats software to calculate - data scattered all around.

Thanks!

Li

• ###### 5. Re: Using table calculations to calculate chi square

Hi Katy,

In the attached workbook you will find worksheets with contingency tables with one and two categorical variables.

The single category worksheet uses the calc above to compute the statistic.

The two category worksheet uses a calc for Pearson's  chi-squared test for homogeneity/independence of multiple categorical populations.

Remember, you'll want to compare this statistic with the chi-squared distribution with degrees of freedom = (rowCount - 1) * (columnCount - 1).

So, for the example, df = 6. Tableau does not yet provide access to this distribution.

I checked that the results are the same as those given by the "chisq.test" function in R.

Scott

• ###### 6. Re: Using table calculations to calculate chi square

Hi, Scott:

Thank you for the post. It looks awesome! I also tested using excel and it gave the same result - one big leap - at least it will give Pearson Chi-square directly, though we still have to compare with degree of freedom. Hope Tableau will bring in the comparison in the future.

One more question at the moment. I am curious to know if Tableau can do one-way anova test (F statistics), say, if volume of sales are different across the regions - 1 continuous dependent variable vs. 1 categorical variable. Thanks again!

Best,

Li

• ###### 7. Re: Using table calculations to calculate chi square

Hi Katy,

Nope, Tableau doesn't do one-way ANOVA yet. It does compute multi-factor ANOVA for regression to help you determine which categorical factors to use in your trend line models. You'll find this in the Trend Model Description dialog.

cheers, Scott

1 of 1 people found this helpful
• ###### 8. Re: Using table calculations to calculate chi square

Thanks, Scott. The question was not started by me so I am not able to close it. Your answer is very helpful. -Li

• ###### 9. Re: Using table calculations to calculate chi square

Great, let me know if you have any more statistical questions.

• ###### 10. Re: Using table calculations to calculate chi square

Hi, Scott:

Hope you don't mind I revisited your worksheet and asked too much. I have to test many sheets using chi-square so I am hoping there is a shortcut to get the chi-square more quickly. I noticed that when you created new fields like [TotalsCol] [TotalsRow] [ChiSquared], you changed the Default Calculation to [Regions] [Categories]. I know this is the most accurate way. But is there some function I can impose on [TotalsCol] [TotalsRow] [ChiSquared] to give window_sum of columns (Table Down), rows (Table Across), and total of the whole table (Table Down and then Across), so I don't have to make changes every time I test new variables?

Thanks a lot for your time!

Best,

Li

• ###### 11. Re: Using table calculations to calculate chi square

Hi Li,

Sorry, I can't think of a way. I tested each step of the calculation against a step by step calculation of the formula in R. Getting the right answer seemed to require all these layers of Calculated fields. But, there are many people who are better at assembling table calcs than I. At some point, I hope there will be sufficient customer interest in hypothesis tests that we will prioritize adding this as a built-in Tableau function.

Scott

• ###### 12. Re: Using table calculations to calculate chi square

Hi, Scott:

No problem. Thank you for looking into this. Hope Tableau will bring more stats function in the future.

Best,

Li

• ###### 13. Re: Using table calculations to calculate chi square

Hi Li,

I just created an idea to add hypothesis tests to Tableau if you would like to vote for it.

Hypothesis Tests (e.g. t-test, chi-squared, ANOVA)

Scott

• ###### 14. Re: Using table calculations to calculate chi square

Thanks. Scott. Definitely gonna vote for it. But the link seems invalid. -Li

1 2 Previous Next