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

Counting relative values

Hello everyone,

I am working in a chart where i need to count the porcentage of people of a field that measure the avarage of knowlodge of the employees.

The total of people are 202, and they are divided in 3 Departments:

Department A: 25

Department B: 105

Department C: 72

There is the expression of the bar chart:

count ([Avg of Knowledge])

And the chart displays the correct value when no filters are applied:

Sem título.png

The user required to create a filter where it is possible to view each value of the volume (in percentage), so i have created the list below, using the field [Avg of Knowledge]:

Capturar.PNG

So, for instance, the user selected all values under 80% to see how many people (in percentage) are under 80%:

Capturar.PNG

After cheking my database, I have the following situation:

- The department A has 25 employees, and 25 are under 80%, so the chart should display 100%;

- The department B has 105 employess, and 98 are under 80%, so the chart should display 93%;

- The department C has 72 employees, and 70 are under 80%, so the chart should display 97%;

But the chart did not displayed to correct values from each department, as you can see below:

Sem título2.png

I am setting the Relative option on this chart.

Anyone could help to solve this problem?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Please check the attached:

Without Selection

Capture.PNG

With Selection:

Capture.PNG

Expression:

=If(GetSelectedCount(Flag) > 0, Count([Avg of Knowledge]) / Count({1}[Avg of Knowledge]), Count([Avg of Knowledge]) / Count(TOTAL [Avg of Knowledge]))

View solution in original post

19 Replies
swuehl
MVP
MVP

Try disabling the relative option and calculate your percentage like

=Count([Avg of knowlegde]) / Count({1} [Avg of knowledge])

Not applicable
Author

Hi Swuehl,

Your solution worked, but when i clean the filters, the 3 departments display 100%:

Sem título.png

Why?


swuehl
MVP
MVP

Clearing  a selection in a list box, so that all values are shown white, is equivalent to all values possible.

Hence in this scenario, the calculation is equivalent to having all list box items selected.

Count of all records divided by the count of all records equals 100%.

What do you want to see instead? 0%

=If(GetSelectedCount([Avg of knowlegde])>0,

Count([Avg of knowlegde]) / Count({1} [Avg of knowledge]),

0)

sunny_talwar

May be this?

=If(GetSelectedCount([Avg of knowlegde]) > 0,

Count([Avg of knowlegde]) / Count({1} [Avg of knowledge]), Count([Avg of knowlegde]) / Count(TOTAL [Avg of knowledge]))

swuehl
MVP
MVP

Right, that should display the chart as shown in the OP (which I already forgot after my first reply).

But to me, it's kind of confusing that the same chart shows different measures depending on selection (personal opinion, you can do what you want).

sunny_talwar

I agree, I wouldn't want to see a chart which shows something with selection and something else without selection. But I thought that he might be looking for getting the original chart when nothing is selected.

swuehl
MVP
MVP

Sorry, Sunny, I was replying to your post, but adressing more the OP, or maybe talking to myself...

Your solution is a correct solution to the requirement, I believe.

sunny_talwar

No apologizes needed Stefan. We still don't know if this is what is required or not. But my comment was to agree with what you mentioned above . I don't see the logic behind what I proposed, but proposed it anyway

Not applicable
Author

Hi guys, thanks for your answers.

Clearing the filters, i was expecting no selection from any filter. so the calculation it was suposed to be equivalent to having any list box items selected.

So, the expected result was the count of all possible values from each department:

Capturar.PNG

When i used the expression suggested by Sunny, the chart displayed the correct values when the filter is cleared, but after applying the filter, the calculation was incorrect, it returned the following result:

Expression:

=If(GetSelectedCount([Avg of knowlegde]) > 0,

Count([Avg of knowlegde]) / Count({1} [Avg of knowledge]), Count([Avg of knowlegde]) / Count(TOTAL [Avg of knowledge]))

Capturar.PNG

Thanks