I have this table:
Survey1:
LOAD * inline [
Respondent, AnswerToQuestion1, AnswerToQuestion2
resp1, 1, 1
resp2, 1, 2
resp3, 2, 1
resp4, 2, 1
resp5, 2, 3
resp6, 2, 1
resp7, 2, 1
resp8, 3, 2
resp9, 3, 2
];
I want to get a 100% stacked bar chart from this data. But it is difficult both, in Excel and in Qlick Sense... These programs do not accept that I would want to summarise one field/column into one bar.
My desired result should look similar to this:
Is there a way to do so in Qlik Sense?
I think you want to alter the way the data is modelled using unpivot/crosstable - which would create a table more like:
Respondent, Question, Answer
resp1, Question1, 1
resp1, Question2, 1
resp2, Question1, 1
resp2, Question2, 2
You could then make a stacked bar chart with 2 dimensions - Question & Answer with count(Answer)/count(TOTAL<Respondent>Answer)
as your measure to give you a percentage. Note: I haven't tested that code - I'd start with a table with those 2 dimensions and count(Answer)
as a measure - then check that count(TOTAL<Respondent>Answer)
gives the total number of answers you'd expect.
In the script editor, assuming your table is called "Answers" this could be done with something like:
Answers_new:
crosstable(Question,Answer,1)
Load * resident Answers;
drop table Answers;
rename table Answers_new to Answers;