How to count total number of responses for each type of response and for each question

[suthashiniv]

Hi,

I have an excel table where there are multiple questions and each question has 6 types of responses. I need to calculate the count of each type of response per question. The excel table is below:

Name	Dept	Q1	Q2
F2	Dept1	4	5
F1	Dept1	5	5
F1	Dept1	5	4

This is what I did in Qlikview.

For question 1, I managed to show that 1 person gave response 4, and 2 of them said 5. However, for Q2, I only managed to show the response given for F2, which is 1 person said 5. For F1, the table should show 1 under 4 and 1 under 5.

The code I used was if (Data=RatingID, count(Data), 0)

How should I change this to show the response from all users? Please help.

Thank you.

[Anonymous · Accepted solution]

Hi Sutha vj,

It seems that when you have two responses for the same question qlik doesn't return any value for your current expression. Instead you could use the aggr function to count the total of responses like the following one:

aggr(count({<RatingID = {1}>} RatingID), Dept, Name, Question, RatingID)

This will return an output like this:

Please, check the attached example.

Regards,

-- Karla

[suthashiniv]

Hi Karla,

Thanks for helping. I have yet to try the suggestion. Will try it soon and let you know if it works.

Thanks again!

[suthashiniv]

Hi Karla,

Thank you so much! That worked.

Thanks for your help! Much appreciated!!