Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Representing the percentage value

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 1126
Option 267
Option 314
Option 413
Option 512
Option 68
Option 76
Option 86
Option 95
Option 104
Option 113
Option 122
Option 131


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 🐵

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
Not applicable
Author

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)

Not applicable
Author

Donut,


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

Not applicable
Author

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 🐵

Not applicable
Author

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.

Not applicable
Author

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 🐵

Not applicable
Author

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.

Not applicable
Author

Yes, that works perfectly, so easy Smile

Thanks 🐵