2 Replies Latest reply on Feb 29, 2012 12:41 PM by osman.javed

    Dynamically Conducting "Lift/Decline %" Analysis on Survey Results using Calculated Fields

      Alright so after many unsuccessful attempts at solving this, I've created a sample file and database hoping someone understands how to do this. Sorry for this post being long but it is a slightly complicated concept. I've tried to make this as easy for you guys as possible But please let me know how I can further clarify. 

       

      What I Want to Achieve:

       

      I am trying to do a Lift/Decline % Analysis on a set of Survey Data I have gathered. The process of doing this analysis is as follows:

       

      1. Select a question and answer which you want to choose as your baseline. This defines your subset of respondants as well as your baseline percentage
      2. Determine your lift/decline % based on how this subset of participants answered your survey in comparison to how the entire all respondents answered the survey. Essentially you are determining how your baseline selection influences peoples response to questions.
      3. The formula for this equation is as follows:
        1. [(# of people who answered a question from the subset of your selection/# of people who answered a question from all survey participants) - baseline %]/baseline %

       

      Example Scenario:

       

      Survey Respondents: 10

      Survey Question 1: Did you buy something? (Y/N)

           Y: 4

           N: 6

      Survey Question 2: Were you satisfied with the fitting room cleanliness? (Y/N)

           Y: 7

           N: 3

       

      You select your baseline scenario as Question: "Did you buy anything today?" and Answer: "Yes"

       

      4 people said Yes they bought something. Therefore your baseline is 4/10 people or 40%.

      Of these 4 people who said "Yes they bought something", 3 were happy with the fitting room cleanliness.     

           So 3 of the 4 people who bought something found the fitting room to be clean and 7 out of all 10 survey respondents found the fitting room      to be clean


      Now the lift/decline calculation would be as follows:

       

      [(3/7)-(4/10)] / (4/10) = 0.07 or 7%

       

       

      What It Should Look Like:

      HBC Screenshot.jpg

       

      Formula used above:

      (sum(IF [OnAverageHowOftenDoYouVisit]="Almost every week" THEN 1 ELSE 0 END)

      / count([OnAverageHowOftenDoYouVisit])

       

      -window_avg(sum(IF [OnAverageHowOftenDoYouVisit]="Almost every week" THEN 1 ELSE 0 END)

      / count([OnAverageHowOftenDoYouVisit])))

       

      /window_avg(sum(IF [OnAverageHowOftenDoYouVisit]="Almost every week" THEN 1 ELSE 0 END)

      / count([OnAverageHowOftenDoYouVisit]))

       

       

      Where I Am Now:

      I am trying to get this calculation to work dynamically on my dashboard. The way I'd like it to work is on the first worksheet, you select your baseline scenario which defines your subset of participants. On the second worksheet, the responses of your defined subset from worksheet 1 are shown. And on the third worksheet, the lift/decline % analysis is conducted.

       

      I have achieved steps one and two of this but am unable to get the 3rd piece of anlaysis to come together. My team has made this work in the past but not dynamically. The screenshot and formula used are shown below.

       

      I have attached my workbook where I am trying to make this work.

       

      I essentially need to have a calculated field that takes Column 2 highlighted below and divides that by Column 1 high lighted below. Then use this percentage in the calculation.

      My Screenshot.jpg