topic Re: Survey data in QlikView

Survey data

Tue, 25 Nov 2014 20:30:22 GMT

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.

Re: Survey data

Anonymous — Tue, 25 Nov 2014 20:36:29 GMT

Count(distinct 1) ?

Re: Survey data

Tue, 25 Nov 2014 20:42:41 GMT

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.

Re: Survey data

Tue, 25 Nov 2014 21:08:02 GMT

Interesting. . . when I use the following:

sum (([Q7])=1)

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

Re: Survey data

Tue, 25 Nov 2014 21:10:35 GMT

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%)

Re: Survey data

Anonymous — Tue, 25 Nov 2014 21:13:41 GMT

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]) ?

Re: Survey data

Tue, 25 Nov 2014 21:24:28 GMT

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]) ?

Re: Survey data

danieloberbilli — Tue, 25 Nov 2014 23:27:55 GMT

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

Re: Re: Survey data

Peter_Cammaert — Tue, 25 Nov 2014 23:27:59 GMT

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

Re: Survey data

Wed, 26 Nov 2014 14:43:04 GMT

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

Re: Survey data

danieloberbilli — Wed, 26 Nov 2014 22:54:54 GMT

All the blogs/documents posted here by Henric Cronström are worth reading. And the books QlikView for Developers and QlikView Cookbook are really good to learn about QlikView.

Kind Regards

Daniel