topic Re: Calculatin data in a group excluding NA data in QlikView

Calculatin data in a group excluding NA data

suthashiniv — Tue, 14 Jul 2015 10:06:10 GMT

Hi,

I am collating the data i have collected into a bar chart whereby i would need to create 3 groups to compare the difference per year. The data i collected are similar to how it is below:

Department	Q1	Q2	Q3
Dept 1	10	10	5
Dept 2	10	5	5
Dept 3	10	5	5
Dept 4	10	NA	5
Average	10	5	5

Each group consists of tables of data whereby some data could be stated as NA. Where the data is NA the final average should be calculated excluding the average, for example, for Q2, the average should be 6.66 and not 5 as the NA value should be excluded and the total sum should be divided by 3 instead.

So currently,since i have 3 group, how I'm deriving to this value is by AVG(AGGR(AVG({[Group 3]}Data),Faculty, Questions,Year))

As i want to exclude data which is NA, i need to include something like Data-='NA', but how do i do it as this is the data from a group. in this scenario, the group name is Group 3.

Please help. Thanks.

Re: Calculatin data in a group excluding NA data

sunny_talwar — Tue, 14 Jul 2015 10:36:46 GMT

Is group an alternate state here?

Re: Calculatin data in a group excluding NA data

suthashiniv — Tue, 14 Jul 2015 13:22:13 GMT

Hi, yes. Group is an alternate state

Re: Calculatin data in a group excluding NA data

sunny_talwar — Tue, 14 Jul 2015 13:31:56 GMT

Does this help:

=Avg({[Group 3]<Data -= {'NA'}>} Aggr(Avg({[Group 3]<Data -= {'NA'}>}Data), Faculty, Questions,Year))

Re: Calculatin data in a group excluding NA data

suthashiniv — Wed, 15 Jul 2015 02:17:04 GMT

No....it returns the same result. thanks