Pivot table - count on rows

[#1]

Hi all,

I am quite new on Qlik Sense... At the moment I'm doing an applikation with information from a survey. The survey has Qustions with subquestions and our information is organized as follows:

Question  |  QuestionID  |  SubQuestion  |  SubQuestionID  |  Answer

Many questions has subquestions with many alternative answers, organized as a matrix.

(To avoid the problem of synthetic keys, I had to rename SubQuestion in the data model --> Question.SubQuestion

I now want to do a Pivot table showing dimension Answers (showing the different answer alternatives: daily, weekly, monthly and so on) on columns and dimension Subquestions on rows for a specific Question. However, I can't get the results correct and don't know how to fix it. At the moment I'm using one measure which only presents the figures of one subquestion. The measure looks like this:
COUNT({$<Question.SubQuestionID={7}>}Answer)

My problem is that I don't know how to write a measure that counts all cells/rows.

Thank you in advance for your input and help!!!

Kind regards

Michelle

[rubenmarin]

Hi Michelle, you tried using just "Count(Answer)"? Why the fixed SubQuestionID=7?

Or maybe: Count(TOTAL <Question> Answer)

Or: Count(TOTAL <Question, SubQuestion> Answer)

[#3]

Hi Ruben!

Thank you for trying to help!

I have tried your suggestions but it doesn't give me the correct information either.

When  I don't specify SubQuestionID, I get all my SubQuestions back, no matter which Question they belong to. I also get all the differens alternatives of answers back. I guess I somehow need to specify what exactly QuestionID and SubquestionID that should be referensed in the table.

I enclose a printscreen of the pivot table trying your last measure, so that you can get a picture...

[rubenmarin]

Hi Michelle, what exactly you want to show? All subquestion '7' with his answers count?

If you can upload a sample app and the desired result I can give more help. I'm not sure if your datamodel is ok, in your initial post seems that QV can't separate the data from Questions, It's like all answers to subquestionID '7' is related to all Subquestion wich has subquestionID=7, no matter which subquestion or question the answer belongs.

[#5]

Hi again Ruben!

I'm so grateful for your help!!!

I will try and upload a sample app but I will have to clean it a bit, so that it contains only a couple of questions (I also made the information more anonymous).

I also enclosed 1 xls.doc with data, 1 sample_app.qvd and a picture of how I am trying to show the information in the pivot table...

Thanks once again!!

[#6 · Accepted solution]

Had to make adjustments in the datamodel to get it right

[rubenmarin]

Great!, Sorry but I missed the alert to this post and wasn't aware of your sample.

Please mark your own answer as correct to close the thread.

[#8]

Will do Ruben! Thanks for your effort!!!