2 Replies Latest reply on Feb 26, 2012 6:48 PM by osman.javed

# Dynamically filtering the inputs of a calculated field

Hello all,

Was hoping for some help in solving an issue I've been trying to tackle.

I am dealing with a large set of survey responses.

The calculation I am hoping to achieve is the following:

//finds the difference in conversion % between each row and the base case, expressed as a % of the base case

//First divide "Yes" reponses by total responses to find Conversion % for the column

((sum(IF [DidYouPurchaseAnythingToday]="Y" THEN 1 ELSE 0 END) / count([DidYouPurchaseAnythingToday]))

//Then subtract the Conversion % for the Pane to find the difference between the column and the base case conversion %'s.

//(the Pane contains all conversion data for an individual question)

-window_avg((sum(IF [DidYouPurchaseAnythingToday]="Y" THEN 1 ELSE 0 END) / count([DidYouPurchaseAnythingToday]))))

//Finally divide by the Pane Conversion % to normalize the difference

/window_avg((sum(IF [DidYouPurchaseAnythingToday]="Y" THEN 1 ELSE 0 END) / count([DidYouPurchaseAnythingToday])))

The formula is essentially saying:

(% of participants who answered question X a certain way given all data - % of participants who answered question X a certain way given the filters applied to the data set)/(% of participants who answered question X a certain way given the filters applied to the data set)

What I am hoping to be able to do is dynamically select the question/criteria against which this percentage is calculated. Currently I have built my dashboard such that I can filter my survey results by question and/or answer and see how the filtered subset of participants answered the rest of the questions.

Use Case Example: You have a set of 100 participants. 80 of these participants bought an item (i.e. 80% of participants bought an item)

 Filter Participant Count: 100 Comment Question: Did you buy an item?, Answer: Y 80 I want to dynamically filter the question I use here

You want to find how many women taking your survey own a car and bought an item

 Filter Selection Participant Count: 100 Comment ***: F; 70 Question 1: Do you own a car? Answer: Y; 60 I want to dynamically filter the question I use here Question 2: Did you buy an item?, Answer: Y 30 I want to dynamically filter the question I use here

You would be left with a subset of 30 participants.

The calculation would then be:

[(30/60)-(80/100)] / [80/100] = -3/8 or -37.5%

Ultimate Goal: I want to be able to dynamically choose the questions I am using to calculate and filter this percentage.

Where I am now: I have two sheets, one which is able to find the total and percentage with only one question selected (i.e., 80 in the figure above). The other sheet finds the total and percentage using both questions selected (i.e., the 30 in the figure above).

I have a third sheet which seeks to take the filtered percentages from each sheet, find the difference, and normalize it to give me my percentage but the filtering/calculated field fails to yeild results. Instead, the numbers on my third sheet end up getting filtered identically result in a calculation of 0.00%

I look forward to your responses. Thank you!

Please let me know if clarification is needed. Sorry but the data we are examining is sensitive and therefore cannot be shared publicly.

• ###### 1. Re: Dynamically filtering the inputs of a calculated field

Hi Osman,

What is the calculation that is being used on the third sheet--the one that returns zero? Are you able to create a mockup so we can get a better idea of what this all looks like?

One thing you might want to play around with--if you haven't already is the Compute Using (right click on the table calculation and you will see this option).

-Tracy

1 of 1 people found this helpful
• ###### 2. Re: Dynamically filtering the inputs of a calculated field

Thanks Tracy for your response. I ended up stumbling upon the Compute Using capability which did help greatly. Now I'm facing another roadblock which I've described here: http://community.tableau.com/thread/116502

Thanks again though for the response.