1 Reply Latest reply on Jun 24, 2016 2:16 PM by Adam.Cogswell

    Filtering pivoted data without messing with database

    Bruno Merino
    Bruno Merino Jun 15, 2016 8:17 AM

      Hello everyone!

       

      This is the first time I post a question in almost 2yrs lurking in the forum, as I found no satisfactory solution to this issue. I'll explain it in detail.

       

      OUR DATASET

       

      Our dataset is composed of 2 tables left joined by customer ID; first one has profile data such as gender, income, etc, etc, and the second contains all questions of the survey, PIVOTED in Excel with the good old Tableau reshaper; the result goes like this:

       

      IDQuestion NumberQuestion     Question ItemAnswer                                      
      1001Q20Question 20Q20_Item01Yes
      1001Q21Question 21Q21_Item01Yes
      1001Q21Question 21Q21_Item02No
      1001Q21Question 21Q21_Item03Yes
      1002Q15Question 15Q15_Item01Yes
      ...............

       

      So, each question can have multiple items to which the customer answers either yes/no and/or numeric answers on a scale from 0 to 10.

       

      OUR PROBLEM

       

      When we use the variables on the first joined table as filters, everything is fine - the challenge is that we must, FOR EVERY QUESTION, be able to filter only the IDs that answered "YES" to each Question_Item of Question 21. The filter would show all Question Items for Q21 and, when I select for example Q21_Item02, the table would show the answer to all questions of those who answered "YES" to that item.

       

      I'm sending the twb attached with what I have so far. I have created a calculated field to serve as filter this way:

       

      If contains([Question_Number], "Q21") then

       

          If contains(UPPER([Answer]),"YES") = true then

       

              [Question_Item]

         

          END

       

      END

       

      No matter how I formulate this filter, all answers to other questions are on the item "null" - obviously, Tableau is retrieving not the ID subset that would serve for all other questions, but the rows containing the Question Items for Q21...

       

      OUR CHALLENGE

       

      I really would hate to mess with the database any further; I do all ETL processing manually, and as we have multiple surveys that are done once and updated on a monthly basis, I have the challenge to make ETL really as simple and quick as possible, having all calculations done in Tableau, either via LOD calculations, parameters or whatever gimmicks Tableau has to offer. I have a strong suspicion that this can be done via some LOD calculation, but as of now, I seem to have reached a dead end.

       

      Any ideas are - truly - welcome!

       

      Thanks everyone,