Dynamically filtering the inputs of a calculated field
osman.javed Feb 21, 2012 1:56 AMHello 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.