Filtering pivoted data without messing with database
Bruno Merino Jun 15, 2016 8:17 AMHello 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:
ID | Question Number | Question | Question Item | Answer |
---|---|---|---|---|
1001 | Q20 | Question 20 | Q20_Item01 | Yes |
1001 | Q21 | Question 21 | Q21_Item01 | Yes |
1001 | Q21 | Question 21 | Q21_Item02 | No |
1001 | Q21 | Question 21 | Q21_Item03 | Yes |
1002 | Q15 | Question 15 | Q15_Item01 | Yes |
... | ... | ... | ... | ... |
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,
-
Pivot_Filter_Test.twbx 48.5 KB