topic Representing the percentage value in QlikView

Representing the percentage value

Thu, 16 Jul 2009 01:20:37 GMT

Hello,

How would I go about creating a chart to represent the percentage value instead of a numeric value. My data is a little different in the respect that I'm counting the presence in the data rather than its value (which is 'yes').

My data is below:

Option 1	126
Option 2	67
Option 3	14
Option 4	13
Option 5	12
Option 6	8
Option 7	6
Option 8	6
Option 9	5
Option 10	4
Option 11	3
Option 12	2
Option 13	1

There were 149 distinct people answering the question and 267 boxes ticked in total - it was a multiple choice question and as many of the options could be ticked as wanted with at least one box having to be ticked.

In a single bar chart I would like to represent each option's percentage value - 84.56% of people clicked Option 1 and 44.967 people clicked Option 2 - how do I put those values on a chart instead of the actual numbers.

If I click 'relative' it shows 47% for Option 1 which is not really what I want to represent - 47% is 126/267*100 rather than 126/149*100.

I can get the distinct count of how many people answered any question for the chart label but when I try to use this value for the definition =count( Distinct ID) the chart just gives me a value for 1 for each option.

I'm sure there's an easy answer (I hope there is) but I'm not able to figure out what I need to do so any help is greatly appreciated.

Thanks 🐵

Representing the percentage value

Thu, 16 Jul 2009 01:25:48 GMT

I suppose what I want to be able to do is to use the value of 149 (in this case) in the expression definition without explicity writing in 149 - what do I need to type that will give me 149. If I can get that I can then change the number format to show in percentage quite happily.

Thanks o)

Representing the percentage value

Thu, 16 Jul 2009 01:47:38 GMT

Donut,

If possible, posting an example QVW will make it much easier to offer an opinion about how to solve this problem

Representing the percentage value

Thu, 16 Jul 2009 01:59:23 GMT

I'm not sure how to do that.

In my expression I have =count( {BM_MyBookmark} Question ) which gives the numeric value.

If I do =count( {BM_MyBookmark} Question ) / 149 I get the value I want.

What can I replace 149 with to get the same value?

In the Script Editor I tried the following which means I can use $IDCount in the label but doesn't seem so good in the expresion:
Set IDCount = count (distinct ID)

The expression result keeps coming out to be 1!

Even if I explicitly set IDCount = 149 it still doesn't work. What would work?

Thanks 🐵

Representing the percentage value

Thu, 16 Jul 2009 02:03:51 GMT

Give this a try. I created a variable to store the number of participants (vParticipants). I did it manually in the load script. It can be done in multiple ways, but from your dataset, there is no way to infer that 149 participated. All we know is that at least 126 did (the number that answered 1).

Once you have the number of participants defined, you take the number of clicks divided by the number of participants and you have your percentage.

I've attached an example.

Representing the percentage value

Thu, 16 Jul 2009 02:18:58 GMT

I'm not able to open your example as I'm using the Personal Edition and it gives me a warning about recovering the file.

The data I gave doesn't give that info but I do know from the full data how many unique entries there have been.

I can count the number of distinct IDs in the expression label, can I use that in the script editor and use the variable in the expression definition?

In the script editor I have this:

Let IDs = 151;

I can then use =count( {BM_MyBookmark} Question ) / IDs in the expression definition.

Can I instead do something like Let IDs = count(DISTINCT ID)?

When I try this I get the following error:

Aggregation expressions not allowed in GROUP BY clause

Let IDs = count(DISTINCT ID)

What can I use instead?

Thanks for your time 🐵

Representing the percentage value

Thu, 16 Jul 2009 02:44:23 GMT

Instead of using a variable like you describe above, you could just make another load.

SQL SELECT COUNT(DISTINCT ID) As IDCount
FROM ID_Table

Then use: =count( {BM_MyBookmark} Question ) / IDCount in your expression.

I would guess that there is a way to do it in the load script with a variable, but I'm not sure how off the top of my head.

Representing the percentage value

Thu, 16 Jul 2009 02:53:59 GMT

Yes, that works perfectly, so easy

Thanks 🐵