topic Re: Ignore dimension modifier in QlikView

Ignore dimension modifier

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

Help! This is driving me mad!!!

I have a straight table with 2 dimensions and 2 expressions

I would like the 2nd expression to only take Dim1 into account. However, I get the following:

Dim1	Dim2	AVG({<GEN={'ALL'}>}AP)	AVG({<GEN={'ALL'}>} TOTAL <Dim1> AP)
1	A	4	4
1	B	1	1
2	B	2	2
2	C	5	5

When I change the dimension in the modifier

Dim1	Dim2	AVG({<GEN={'ALL'}>}AP)	AVG({<GEN={'ALL'}>} TOTAL <Dim2> AP)
1	A	4	4
1	B	1	1.5
2	B	2	1.5
2	C	5	5

I get what I would expect (see values for Dim2=B).

But with the first table (with Dim1 in modifier), I am not getting what I would expect.

What I want to see is:

Dim1	Dim2	AVG({<GEN={'ALL'}>}AP)	AVG({<GEN={'ALL'}>} TOTAL <Dim1> AP)
1	A	4	2.5
1	B	1	2.5
2	B	2	3.5
2	C	5	3.5

Where am I going wrong?!

sunny_talwar — Wed, 21 Jun 2017 12:37:58 GMT

Would you be able to share a sample where you seeing this issue?

vishsaggi — Wed, 21 Jun 2017 12:40:19 GMT

The expression seems to be correct.

vinieme12 — Wed, 21 Jun 2017 13:03:44 GMT

change the expression to sum() / Count()

sum({<GEN={'ALL'}>} TOTAL <Dim1> AP)

Count({<GEN={'ALL'}>} TOTAL <Dim1> AP)

sunny_talwar — Wed, 21 Jun 2017 13:08:53 GMT

But I don't see why Avg() won't work here

sunny_talwar — Wed, 21 Jun 2017 13:09:44 GMT

Another option could be to use Aggr(),

Aggr(NODISTINCT Avg({<GEN = {'ALL'}>} AP), Dim1)

jessica_webb — Wed, 21 Jun 2017 14:24:59 GMT

Agreed that it seems to be the correct expression, so no idea why it won't work!

But the Aggr() function has worked to give me the outcome I was expecting, so thank you very much!