topic Re: Help for a QlikView newbie.... in QlikView

Help for a QlikView newbie....

Fri, 20 Nov 2020 18:02:30 GMT

I'm trying to manipulate some data and having a little bit of trouble. I currently have data with several columns, including region (Europe, Asia....etc), question and answer (1 through 5).

What I'm trying to do is get the average answer for all questions from a given region. For instance, I want to get an average of the ansers for every question in our 'Europe' region. Can anybody point me in the correct direction on how to do this? I wanted to create a gauge chart to display the average, but having issues just getting started.

Thanks in advance,

Tim

Re: Help for a QlikView newbie....

sinanozdemir — Tue, 20 Oct 2015 18:34:54 GMT

Hi,

Can you post some sample data?

Thanks

Re: Help for a QlikView newbie....

sunny_talwar — Tue, 20 Oct 2015 18:35:28 GMT

So I guess you would want a single gauge chart for each country?

You can create a guage chart with country as your dimension and then use Avg(Answer) as expression and then use trellis option to see a gauge chart for each country.

I hope that make sense.

Best,

Sunny

Re: Help for a QlikView newbie....

Tue, 20 Oct 2015 18:37:41 GMT

How many columns you want to show?

Provide a picture of what you are expecting.

Regards,

Anjali Gupta

Re: Help for a QlikView newbie....

Tue, 20 Oct 2015 18:41:37 GMT

Thanks Sunny (and everybody else!) I did set up my Region (country) as the dimension, so at least I was on the right path

What I need to show is the percentage of the answers that are equal to a value -- in this case, "Satisfied". So I need to find out what percentage of the total number of answers for a Country were reported as "Satisfied". Can I still do that with just Avg?

Re: Help for a QlikView newbie....

Tue, 20 Oct 2015 18:47:15 GMT

Satisfied	201405013	English	GroupAudioConfExternal	Asia	3278213832	SBU3
Satisfied	201405013	English	GroupAudioConfExternal	Asia	3286480866	SBU1
Satisfied	201405013	English	SelfVideoConferencing	Asia	3278213832	SBU3
Satisfied	201405013	English	SelfVideoConferencing	Asia	3286480866	SBU1
Doesnt Meet Needs	18698416	English	GroupAudioConfExternal	Asia	3534764509	SBU2
Doesnt Meet Needs	18698416	English	GroupAudioConfExternal	Asia	3536532925	SBU1

Re: Help for a QlikView newbie....

Tue, 20 Oct 2015 21:44:35 GMT

Hello Timothy,

You can accomplish this with set analysis:

assuming fields = answer and ID

count({<answer = {'Satisfied'}>}Distinct ID) / count(Distinct ID)

This will give you percentage of Satisfied vs. Total

Re: Help for a QlikView newbie....

MarcoWedel — Tue, 20 Oct 2015 22:38:40 GMT

Hi,

another solution could be to use the display option "circular gauge" in a pivot table:

hope this helps

regards

Marco

Re: Help for a QlikView newbie....

Tue, 20 Oct 2015 23:04:19 GMT

Have a look to this blog

Average – Which average?

Re: Help for a QlikView newbie....

Wed, 28 Oct 2015 14:18:06 GMT

Hi David,

Thanks for the reply. I think you've pointed me in the right direction, but I'm worried that my needs might be a little bit more complex (I hope not). So I do have the "Answer" field, and instead of ID, I think I can use "Question" (there are multiple questions that each user has to answer). But I want to make sure that I'm only counting the answers that are coming from the Asia region (for this example).

So I need the number of "Satisfied" responses from all users for all questions from the Asia region divided by the total number of questions that were answered in the Asia region.

Does what I'm trying to do make sense?

Thanks again for all of the help so far.

Tim

Re: Help for a QlikView newbie....

Wed, 28 Oct 2015 15:28:15 GMT

OK - so you are looking to have multiple gauges, one for each continent?

You can add the continent to the set analysis like so:

count({<Answer = {'Satisfied'},Continent = {'Asia'}>}Distinct Question)

count({<Continent = {'Asia'}>}Distinct Question)

As an alternative, you can simply use "count({<answer = {'Satisfied'}>}Distinct Question) / count(Distinct Question)" I suggested in a heavily formatted bar chart. See Attached !

Re: Help for a QlikView newbie....

Wed, 28 Oct 2015 15:53:05 GMT

Thanks, David!

I'm now at least getting the gauge to show up, which is great. I think I have to tinker around with the expression to get it correct (as it's currently giving me 0 as the answer). One (hopefully) last question -- Should I need to add the Region={'Asia'} to the "count(Distinct Question)" part to ensure that I'm only counting the total number of questions that were sent to the Asia region?

Thanks,

Tim

Re: Help for a QlikView newbie....

Wed, 28 Oct 2015 16:19:33 GMT

Hi Tim,

Yes, you need to reduce the ‘set’ of the total to reflect only Asia.

David Freschl