topic Re: Percent of Group Average Accross Periods in QlikView

Percent of Group Average Accross Periods

Fri, 17 Apr 2015 16:39:57 GMT

Hi,

I am trying to compare individual performance to their group's average performance across periods.

I have the following formulas:

1. Metric: sum(Metric)

2. Group Average:

if(Metric > 0,

Sum({$<Individual= >} TOTAL <Group,Period> Metric)/

COUNT({$<Individual= >} TOTAL <Group,Period> Metric)

)

These work as intended in a straight table. I can either have both an individual and their group selected, or just the individual and the Group Average still works

Group	Period	Individual	Metric	Group Average
			131
b	0	n	68	67.5
b	1	n	63	61

However, when I change this table to a graph, if I select an individual and leave the group selection at the default, it shows the average across all groups (~36) instead of just that individual's group's average.

I have attached my document for reference.

My real data has about 5 groups, and the periods most often used will be weeks.

Thank you,

Elin

Re: Percent of Group Average Accross Periods

sunny_talwar — Fri, 17 Apr 2015 17:05:49 GMT

That is because you are telling your expression to include all the employees regardless of any selection made on employee list box. Try this:

=If(Metric > 0, Sum({$} TOTAL <Group,Period> Metric)/COUNT({$} TOTAL <Group,Period> Metric))

HTH

Best,

Sunny

Re: Percent of Group Average Accross Periods

Fri, 17 Apr 2015 17:11:21 GMT

Hi,

I want to include all individuals that belong to a given group. I want the average of all indivuduals who belong to a group, for each period.

That formula returns the following:

Group	Period	Individual	Metric	Group Average	Group Average v2
			131
b	0	n	68	67.5	68
b	1	n	63	61	63

Thanks

Re: Percent of Group Average Accross Periods

sunny_talwar — Fri, 17 Apr 2015 17:14:19 GMT

Is this the output you are expecting to see? the one you just posted now?

Best,

Sunny

Re: Percent of Group Average Accross Periods

Fri, 17 Apr 2015 17:16:34 GMT

The output I'm expecting to see is in the Group Average Column. The Group Average v2 returns that individuals performance when selecting only one individual, which is wrong.

The problem is that the table and the graph appear to behave differently.

Re: Percent of Group Average Accross Periods

Fri, 17 Apr 2015 17:31:02 GMT

A theory I have as to why it isn't working in the graph is that Group isn't appearing on the x-axis. I don't know how to get group to appear.

Re: Percent of Group Average Accross Periods

sunny_talwar — Fri, 17 Apr 2015 17:51:19 GMT

It's weird, but seems like it may be a function of your dimensions here. I am unable to figure out why it is happening, but I hope someone else might help.

Best,

Sunny

Re: Percent of Group Average Accross Periods

Anonymous — Fri, 17 Apr 2015 18:45:33 GMT

You are using a line chart and even if you add more than 1 dimensions to I guess it would calculate your expressions with regard to first dim only.

Try to use a bar chart instead,

Hope it helps.

Re: Percent of Group Average Accross Periods

ramoncova06 — Fri, 17 Apr 2015 19:17:37 GMT

if you do not want to use a combo chart you can use the following expression


	sum({<Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >}),Individual=,Metric= {">$(=(0))"}>} TOTAL <Group,Period> Metric)
	/
	COUNT({<Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >}),Individual=,Metric= {">$(=(0))"}>} TOTAL <Group,Period> Metric)

Re: Percent of Group Average Accross Periods

Fri, 17 Apr 2015 20:06:37 GMT

Thank you!

--Edited after I stared at the formula a bit longer--

So that I can understand better how to use similar capabilities in the future, can confirm for me through how the following section works?

{<Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >}),Individual=,Metric= {">$(=(0))"}>}

Between the {} there are 3 different fields that will be filtered:

Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >})

means filter for the groups that the individual has resided in at a given point

Individual=

means filter for all individuals regardless of current selection

Metric= {">$(=(0))"}

means filter for Metrics that are greater than 0. This is equivalent to the If statement in my original formula.

Thanks again

Re: Percent of Group Average Accross Periods

ramoncova06 — Fri, 17 Apr 2015 20:22:14 GMT

so the expression in the set analysis is split in three parts

Group = P({<Individual={"$(=(GetFieldSelections(Individual)))"} >})
- Your original problem was that you were excluding Individual from the expression and because you were not selecting any group QV was adding the total for both groups, with this we are telling QV to use the group that is associated with the Individual field
Individual =
- this is to bring all Individu
Metric= {">$(=(0))"}
1. since you were using an IF for Metric > 0, I used an advanced search instead of the if, this performs faster than IF's