I have a set of data/results from a survey with several users. I'm trying to crate a set of data by each user as the table below illustrates.
This is the source data of one of the users.
User
Ans_Text
Response_Level_2
Ans_Minutes
8194
Collab
Noise
15
8194
Collab
Privacy
15
8194
Develop
Noise
10
8194
Focus
Noise
25
8194
Focus
Privacy
20
8194
Focus
Quiet Area
20
8194
Network
Noise
5
I would like to create a table that takes the USER as the dimension and where Ans_Text='Focus' return the name in Response_Level_2 and then return the value in Ans_Minutes so that it looks like this in the table. Ideally to have the resulting table in order highest result first.
User
Focus 1
Focus 1 Min
Focus 2
Focus 2 Min
Focus 3
Focus 3 Min
Focus 4
Focus 4 Min
Focus 5
Focus 5 Min
8194
Noise
25
Privacy
20
Quiet Area
20
-
0
-
0
In some cases there will be a User with 5 results for the Ans_Text response of Focus so when there is less I want to return a - value or 0 for the items in the table.
I have a drafted a test application which will meet your requirements. The only thing is the expressions are all hard coded making the table static and not dynamic. Are there only 5 distinct responses for the focus Ans_text? If so you can as in my example hard code these responses as expressions within a straight table.
See my example for more details. You will need to modify the last 4 expressions of the chart to change the 'Area x' to the correct response.
The table I'm trying to create has all hard coded expression - it is a set format we want to produce on many surveys we have.
There are more that 5 possible answers for the focus Ans_text - something like 28 possibilities and that's whay I was wondering how I can take the top 5 values and there mins associated with the category?
So I'm thinking each expression for e.g Focus 1, will need to pull the first response based on the highest value of mins then Focus 2 to pull the second response based on the second highest value of mins and so on.
