topic Re: Counting relative values in QlikView

Counting relative values

Wed, 25 Nov 2020 16:16:04 GMT

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:

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]:

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

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:

I am setting the Relative option on this chart.

Anyone could help to solve this problem?

Thanks

Re: Counting relative values

swuehl — Wed, 25 May 2016 19:52:53 GMT

Try disabling the relative option and calculate your percentage like

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

Re: Counting relative values

Wed, 25 May 2016 20:06:04 GMT

Hi Swuehl,

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

Why?

Re: Counting relative values

swuehl — Wed, 25 May 2016 20:14:32 GMT

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]),

Re: Counting relative values

sunny_talwar — Wed, 25 May 2016 20:26:43 GMT

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]))

Re: Counting relative values

swuehl — Wed, 25 May 2016 20:33:20 GMT

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).

Re: Counting relative values

sunny_talwar — Wed, 25 May 2016 20:34:56 GMT

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.

Re: Counting relative values

swuehl — Wed, 25 May 2016 20:38:48 GMT

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.

Re: Counting relative values

sunny_talwar — Wed, 25 May 2016 20:40:57 GMT

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

Re: Counting relative values

Tue, 31 May 2016 14:52:19 GMT

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:

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]))

Thanks

Re: Counting relative values

sunny_talwar — Tue, 31 May 2016 14:56:49 GMT

May be try this:

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

Re: Counting relative values

Tue, 31 May 2016 15:01:53 GMT

Wiht this expression the chart seems frozen, the results didn´t change when i try to filter some value from the list box.

Re: Counting relative values

clondono — Tue, 31 May 2016 15:14:14 GMT

Hi Rodrigo,

Try this:

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

Re: Counting relative values

sunny_talwar — Tue, 31 May 2016 15:33:16 GMT

Rodrigo‌ you replied to Stefan that this worked when you make a selection

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

and my solution worked when no selection is made?

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

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

But all I did was to add the condition that if nothing is selected show
Count([Avg of knowlegde]) / Count(TOTAL [Avg of knowledge])

Else if something is selected show what Stefan provided

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

Would you be able to verify if what Stefan provided initially worked or not?

Re: Counting relative values

Tue, 31 May 2016 17:43:40 GMT

Hi Sunny,

I don´t know why, but the condition proposed don´t display the same results as the results when i use them isolated.

The expression proposed by Stefan (=Count([Avg of knowlegde]) / Count({1} [Avg of knowledge])) worked when i make a selection (in this case i selected all the values that are under 80%):

If i clear all selections, the chart displays all bars as 100%, because as explained by swuehl, clearing a selection in a list box the calculation is equivalent to having all list box items selected.

When i use you solution worked when no selection is made, but when i select the same values under 80%, i have the following result:

As you can see, the chart didn´t display the values 100%, 93% and 97% as displayed in the solution proposed by Stefan.

Re: Counting relative values

sunny_talwar — Tue, 31 May 2016 17:45:11 GMT

Would it be possible for you to share a sample?

Re: Counting relative values

Tue, 31 May 2016 17:49:07 GMT

Hi Carlos,

Unfortunately your solution worked only when no selections are made.

After making the same selection under 80%, i had the following results:

Re: Counting relative values

Tue, 31 May 2016 18:50:29 GMT

Follow attached the sample!!

Re: Counting relative values

sunny_talwar — Tue, 31 May 2016 18:53:45 GMT

Please check the attached:

Without Selection

With Selection:

Expression:

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

Re: Counting relative values

Tue, 31 May 2016 18:59:40 GMT

Thanks a lot Sunny!

My mistake: I was not adding the field "Flag" in the condition:

=If(GetSelectedCount(Flag) > 0