topic Re: Percentages not correct when using subfields in QlikView

Percentages not correct when using subfields

Sun, 15 Sep 2013 19:57:58 GMT

Hi there,

I've been trying to solve this for a while but with no luck.

I have made an example (attached) of using a subfield.

In the example - each 'ID' is a person, who has been able to select up to 3 favurite colours.

The problem is that the percentages on the graph work out the % using total entries in the subfield DIVIDED BY the current selection.

I.e.If you mose over Green you'll see it says 22% and we can see that of our 11 people, 6 have selected Green, so it should be over 50%

Is there any way that I can make Qlikview show the percentage of People who selected that Colour?

Any help would be really appreciated,

Thanks,

Andrew

Re: Percentages not correct when using subfields

jolivares — Mon, 16 Sep 2013 01:20:31 GMT

I think you need to mark "Relative" in the expressions tab.

Try to mark and see what happens.

Re: Percentages not correct when using subfields

tresB — Mon, 16 Sep 2013 05:21:02 GMT

May be like attached? PFA

Re: Percentages not correct when using subfields

Mon, 16 Sep 2013 08:05:52 GMT

Hi Juan,

Sadly not that easy. Qlikview automatically splits this data out into one line per entry on the subfield - In the example I've hit relative and it'll give yu the percentage of the total entries for that subfield, not the number of people. Any othe rsuggestions at all?

Thanks,

Andrew

Re: Percentages not correct when using subfields

Gysbert_Wassenaar — Mon, 16 Sep 2013 08:16:49 GMT

Try sum(Count)/count({<Count={'*'}>}total distinct ID). See attached qvw.

Re: Percentages not correct when using subfields

Mon, 16 Sep 2013 08:18:20 GMT

Hi Tresesco,

Thaks for submitting, but in your example (I'll use the colour green again)

I want it to show the percentage of respondents that like green, not the percentage of the total number of favourite colours.

Ie, the bar for green should show 6/11 = 55%

Is there any way around this? I would just hard-code to have =Sum(Count)/11... but need it to keep updating

Thanks

Andrew

Re: Percentages not correct when using subfields

johanlindell — Mon, 16 Sep 2013 08:23:32 GMT

Hi!

What I think you should be trying is the TOTAL keyword where you also can defined which field you should use for the Total. Attaching file

Brgds, Johan

Re: Re: Percentages not correct when using subfields

tresB — Mon, 16 Sep 2013 08:28:14 GMT

PFA.

Anyway you have to keep track of the repeating colors(here only one 'Green' as you told) and add that to count which is a denominator. Here, i have simply added 1.

Edit: I totally missed your field count behavior, you can simply try:

Sum(Count)/Sum(TOTAL Count)

Re: Re: Percentages not correct when using subfields

Tue, 24 Sep 2013 12:16:48 GMT

Hi All,

Thanks for all these, though I;ve just noticed another problem whilst working on a different part of my model which can be expalined here.

If I go onto any of the examples above and select a particular country then the percentages stop working (they all go to 100% rather than displaying the percentage of the current selection.

Does anyone know how I can have a 'percentage of current selection'. I have this issue a lot throughout my data so a solution would be really useful.

Thanks,

Andrew

Re: Re: Re: Percentages not correct when using subfields

johanlindell — Tue, 24 Sep 2013 17:03:05 GMT

Hi Andrew,

I think you should consider moving the count to the colors-field. It alls boils down to want you want to show. See attached. Or is it something else you are after?

Best Regards

Johan