Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Survey data

Just downloaded this today and am testing its functionality with survey data.  I have a field that I marked as a measure called "Q7".  The possible responses to this question in the survey were: yes, no, or unsure and are coded 1,2,3 respectively.  How do I get a count of the yes (1) responses and even more specifically, I want to know the % of yes responses from the total by month in a bar chart.  I have the dimension set up as month and Q7 as the measure but it gives me the TOTAL count for the month.

I imagine it must be an expression that I don't know how to code.  Any assistance will be greatly appreciated.

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: Re: Survey data

That's because the set analysis spec should go before the DISTINCT keyword. And BTW there shouldn't be any DISTINCT keyword at all, because when you do use it, the result will be 1. Use this instead:

=Count({<Q7 = {1}>} [Q7])


To get a ratio, use


=Count({<Q7 = {1}>} [Q7]) / count([Q7])


and display as a percentage.


Best,


Peter

View solution in original post

10 Replies
Highlighted
Creator
Creator

Re: Survey data

Count(distinct 1) ?

Highlighted
Not applicable

Re: Survey data

I know there are 77 responses and it should return 51 yes.  When I copy and paste "Count(distinct 1) in the expression field, it returns a 1.  Ideally, I'd like it to return 66% (51/77).  So, I'm still not yet there.

Highlighted
Not applicable

Re: Survey data

Interesting. . . when I use the following:

sum (([Q7])=1)

I get the correct value but it is shown as a negative (-51)

Highlighted
Not applicable

Re: Survey data

So multiplying by a negative 1 gives the correct value

sum (([Q7_1])=1)*-1

but I have to believe there is a better way & it still does not get me to the ultimate value I'm looking for (66%)

Highlighted
Creator
Creator

Re: Survey data

Let's see if we can't get your 51 first. What i actually meant by [1] (and should've verbalized) is [field used to contain yes response]

Count(distinct({$<Field = {1}>} [Field])  ?

Highlighted
Not applicable

Re: Survey data

I appreciate your patience with me but unfortunately, I'm not a programmer so I don't know what may be missing as the following generates an "Error in expression" message:

Count(distinct({$<Q7= {1}>} [Q7])  ?

Highlighted
Specialist II
Specialist II

Re: Survey data

Hi Mike,

try it without distinct or at least write the 'distinct' after the set expression: count({<Q7={1}>} Q7)  or  count({<Q7={1}>} distinct Q7)

BTW I am specialised in market research and consider QlikView as a great tool to report/analyse survey data. But there are quite some things you have to consider: e.g. think about what you are actually counting...I guess you would rather like to count persons (or occasions/cases) than a value within a certain question. Especially when taking weighting factors/sub-samples into consideration.

Kind Regards

Daniel

Highlighted

Re: Re: Survey data

That's because the set analysis spec should go before the DISTINCT keyword. And BTW there shouldn't be any DISTINCT keyword at all, because when you do use it, the result will be 1. Use this instead:

=Count({<Q7 = {1}>} [Q7])


To get a ratio, use


=Count({<Q7 = {1}>} [Q7]) / count([Q7])


and display as a percentage.


Best,


Peter

View solution in original post

Highlighted
Not applicable

Re: Survey data

Thanks Daniel.

This is a simple survey filled out at an event.  So there is no weighting involved.  I used this as a test case to play with the QlikView functionality.  If it works for this purpose then I'll figure out how to work with my more sophisticated weighted surveys.  Any tips or best practices to help accelerate my learning curve?

Best,

Mike