8 Replies Latest reply on Jan 8, 2014 1:31 PM by Craig Rudick

# Calculations with Aggregated Fields - Odds Ratio

I am trying to use two values from aggregated fields in a new calculation.  Specifically, I want to calculate an odds ratio from two aggregated proportions.

In the example packaged workbook I have created the aggregated fields 'prop_regular_air' and 'odds_regular_air' which calculates the proportion and odds, respectively, that the Ship Mode is 'Regular Air' (odds is just basically just a different way of expressing a proportion).  I now want to find the ratio of two of these odds values.  In the example, the odds that an item is shipped by Regular Air in the East Region is 3.3387 if it is in the Technology Department and 2.5955 if it is Not Technology (note that I created a calculated field to recode the Departments).  I want the ratio of these two odds for each Region (i.e. 3.3387 / 2.5955= 1.2863 in the East).

I've seen a few brief mentions that this kind of thing should be possible, but nothing clearly explaining the process.  Can anyone help?  And let me know if anything is unclear.

Thanks,

Craig

• ###### 1. Re: Calculations with Aggregated Fields - Odds Ratio

Not sure how you want the presentation to be but this could be one approach.

The Odds Ratio Calc for the example below is:

[odds_regular_air]/lookup([odds_regular_air],-1)

//compute using table across. If you'd prefer Odds Ratio be a third column it can be done with custom SQL (or possibly other ways).  I can add the steps if you'd like but it's a little hacky IMO 1 of 1 people found this helpful
• ###### 2. Re: Calculations with Aggregated Fields - Odds Ratio

Boy, thank you for posting this Aaron.  I lost my sanity a little trying to work on this yesterday--I knew there was a simple solution, but for whatever reason, couldn't remember the LOOKUP function.  Helped me learn/review, so thanks again!

• ###### 3. Re: Calculations with Aggregated Fields - Odds Ratio

lol thanks Matt, glad to help, although I had more fun figuring out my second solution.

• ###### 4. Re: Calculations with Aggregated Fields - Odds Ratio

Can you post your workbook?  I wouldn't mind taking a look at your work.

• ###### 5. Re: Re: Calculations with Aggregated Fields - Odds Ratio

Sure thing

The SQL could be better if I spent some time on it but this was an easy way to get a container for the Odds Ratio into the Technology Dimension.

• ###### 6. Re: Calculations with Aggregated Fields - Odds Ratio
Looks pretty clever to me.  Thanks for sharing!
• ###### 7. Re: Calculations with Aggregated Fields - Odds Ratio

Thanks, Aaron!  That is very helpful. Combined with this video, Aggregate Calculations | Tableau Software, I was able to figure out everything I wanted to do (so far, anyway).

Using your Odds Ratio calculation as an example of how to manipulate aggregated calculations, I have managed to create a calculated field which measures the statistical significance of two the difference between two proportions (using the two-proportion Z-test).  I am working on a chart where I can create sub-groups of students who are taking certain classes, and test whether or not their outcomes are statistically different.

• ###### 8. Re: Calculations with Aggregated Fields - Odds Ratio

Ok, I spoke a bit too soon.  While I've got some of the functionality I need, I don't have all of it.  In the updated workbook, the 'Table' sheet has the table of a bunch of values I've calculated for each Region.  In 'Plot1' I make a simple plot of the Odds Ratio and everything looks fine.  The Odds Ratio is correct (in the 'Technology' column anyway) and I can color the regions by their Odds Ratio value (using Odds Ratio 2, which selects the 'Technology' value for both).

But things break in 'Plot2', which is closer to the final product I want to make.  The Odds Ratio is no longer correct.  I (sort of) understand why it's breaking, since the Department_Technology field is no longer in the Columns.  But I need to be able to manipulate the plots without my calculations breaking.  Any more thoughts?

Thanks again,

Craig