Stacked bar chart in Qlik Sense: one bar for one field

Question

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?

Answer 1

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;