Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Count(distinct 1) ?
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.
Interesting. . . when I use the following:
sum (([Q7])=1)
I get the correct value but it is shown as a negative (-51)
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%)
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]) ?
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]) ?
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
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
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