topic Re: result of avg() dimension differs from expectation in QlikView

result of avg() dimension differs from expectation

citizenk — Sat, 16 Nov 2024 04:43:01 GMT

Dear all,

I have difficulties with the AVG() function.

I created an expression which gives me a coverage

Expression "coverage":

Count({$<PCHA_VALUE_GB-={'-'}>}PBGA_BG_NO) / max(aggr(nodistinct Count(PBGA_BG_NO), PAGA_GA_ID))

Now I want to calculate the average of this expression as a dimension but the result differs from the total average I can set in the presentation tab:

Dimension "average"

avg(total aggr(Count({$<PCHA_VALUE_GB-={'-'}>}PBGA_BG_NO) / max(aggr(nodistinct Count(PBGA_BG_NO), PAGA_GA_ID)), PCHA_CHAR_ID, PAGA_GA_ID))

My expectation is that I receive 0,43. Instead I receive 0,3000811.

chart

Any help is appreciated.

Thanks.

Re: result of avg() dimension differs from expectation

sunny_talwar — Fri, 18 Jan 2019 14:46:03 GMT

I think you have a third dimension (Characterstic) which you have not added to your Aggr() function. Can you add that and see if that resolves your issue?

Re: result of avg() dimension differs from expectation

citizenk — Fri, 18 Jan 2019 14:55:22 GMT

This is a calculated dimension, however when I include the source of this dimension, no changes are visible:

avg(total aggr(Count({$<PCHA_VALUE_GB-={'-'}>}PBGA_BG_NO) / max(aggr(nodistinct Count(PBGA_BG_NO), PAGA_GA_ID)), PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))

Re: result of avg() dimension differs from expectation

sunny_talwar — Fri, 18 Jan 2019 14:59:15 GMT

Can you try this

Avg({<PCHA_PUBL = {1}>} TOTAL Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}, PCHA_PUBL = {1}>} PBGA_BG_NO)/
Max({<PCHA_PUBL = {1}>}Aggr(NODISTINCT Count({<PCHA_PUBL = {1}>}PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))

Or this

Avg({<PCHA_PUBL = {1}>} TOTAL Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}>} PBGA_BG_NO)/
Max(Aggr(NODISTINCT Count(PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))

Re: result of avg() dimension differs from expectation

citizenk — Fri, 18 Jan 2019 15:01:35 GMT

Thank you. Both work perfectly.

Re: result of avg() dimension differs from expectation

citizenk — Tue, 22 Jan 2019 10:43:06 GMT

Hi Sunny,

this solution works well when I have only one value in the field PAGA_GA_ID selected.

However when I select more than one it calculates an average of all visible though I need an average for each field PAGA_GA_ID.

I tried to amend the aggregation but I failed until now.

=num(
Avg({<PCHA_PUBL={1},PCHA_CHAR_ID = {"=Count(DISTINCT PCHA_CHAR_ID=PCGA_CHAR_ID) > 1"}>}
TOTAL Aggr(
Count({$<PCHA_VALUE_GB -= {'-'}>} PBGA_BG_NO)
/
Max(Aggr(NODISTINCT Count(PBGA_BG_NO), PAGA_GA_ID))
, PCHA_CHAR_ID, PAGA_GA_ID)),'##0 %')

Thank you in advance.

May you help how I have to adjust the expression to have an average for each PAGA_GA_ID:

Re: result of avg() dimension differs from expectation

sunny_talwar — Tue, 22 Jan 2019 12:09:48 GMT

Try one of these

Avg({<PCHA_PUBL = {1}>} TOTAL <PAGA_GA_ID> Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}, PCHA_PUBL = {1}>} PBGA_BG_NO)/
Max({<PCHA_PUBL = {1}>}Aggr(NODISTINCT Count({<PCHA_PUBL = {1}>}PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))

Or this

Avg({<PCHA_PUBL = {1}>} TOTAL <PAGA_GA_ID> Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}>} PBGA_BG_NO)/
Max(Aggr(NODISTINCT Count(PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))

Re: result of avg() dimension differs from expectation

citizenk — Tue, 22 Jan 2019 12:15:57 GMT

Sadly, they return an "//Error in calculated dimension" though the expression is considered as OK.

Re: result of avg() dimension differs from expectation

sunny_talwar — Tue, 22 Jan 2019 12:24:34 GMT

When did you switch from using an expression to a calculated dimension? or were you always using this as a calculated dimension?

Re: result of avg() dimension differs from expectation

sunny_talwar — Tue, 22 Jan 2019 12:25:32 GMT

Give this a try

Aggr(
Avg({<PCHA_PUBL = {1}>} TOTAL Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}, PCHA_PUBL = {1}>} PBGA_BG_NO)/
Max({<PCHA_PUBL = {1}>}Aggr(NODISTINCT Count({<PCHA_PUBL = {1}>}PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))
, PAGA_GA_ID)

Re: result of avg() dimension differs from expectation

citizenk — Tue, 22 Jan 2019 12:34:37 GMT

This gives the same result for both PAGA_GA_ID like in the picture above while it should differ per PAGA_GA_ID.

Re: result of avg() dimension differs from expectation

sunny_talwar — Tue, 22 Jan 2019 12:36:46 GMT

Yes it was my bad once again.... remove TOTAL qualifier...

Aggr(
Avg({<PCHA_PUBL = {1}>} TOTAL Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}, PCHA_PUBL = {1}>} PBGA_BG_NO)/
Max({<PCHA_PUBL = {1}>}Aggr(NODISTINCT Count({<PCHA_PUBL = {1}>}PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))
, PAGA_GA_ID)

Re: result of avg() dimension differs from expectation

citizenk — Tue, 22 Jan 2019 12:45:49 GMT

Thanks a lot, that does the job!
I hope I can utilise this for all the according calculations I need.

Re: result of avg() dimension differs from expectation

citizenk — Thu, 24 Jan 2019 06:30:34 GMT

Hi Sunny,

My aplogies for coming up with this topic again but I encounter another issue with this since the values differ.

Having one PAGA_GA_ID selected everything is OK, but having multiple PAGA_GA_ID selected, the values differ and I can't even reproduce how they have been calculated.

My efforts to alter the most outer aggregation in terms of fields to be considered failed and I can't find anything helpful in the community board.

May you assist me once more?

Thanks

Re: result of avg() dimension differs from expectation

sunny_talwar — Thu, 24 Jan 2019 12:13:56 GMT

It would be easier to help if you are able to share your qvw file.

Re: result of avg() dimension differs from expectation

citizenk — Thu, 24 Jan 2019 16:01:42 GMT

Hi Sunny,

to share the file is rather difficult (company data) and I have no idea how I could break it down best possible. Maybe a further explanation helps:

In this chart I calculate a coverage and my focus is the coverage proposed ch..(..aracteristics)

I populated the dimension "coverage all characteristics" successully with the expression in the picture (again here):

=num(Aggr(
Avg({<PCHA_PUBL = {1}>} Aggr(

Count({$<PCHA_VALUE_GB -= {'-'}, PCHA_PUBL = {1}>} PBGA_BG_NO)/
Max({<PCHA_PUBL = {1}>}Aggr(NODISTINCT Count({<PCHA_PUBL = {1}>}PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))
, PAGA_GA_ID), '##0,0 %')

The oly distinguishing fact between the two columns " coverage all..." and "coverage proposed..." is that proposed needs an additional condition where I delimit the characteristics (PCHA_CHAR_ID) that exceed a certain count since they also exist in another field:

PCHA_CHAR_ID = {"=Count(DISTINCT PCHA_CHAR_ID=PCGA_CHAR_ID) > 1"}

These are the orange columns of the second row and this is what I need the average of.

I think it all comes down to the question where to add this line into the statement more above to make it work since the dimension "coverage all characteristics" is working.

Even when I currently select a certain value, the result is wrong since I expect 45.75% as "coverage proposed..." but end up with 47.3%

I hope this helps.

Re: result of avg() dimension differs from expectation

sunny_talwar — Thu, 24 Jan 2019 17:11:37 GMT

What exactly is this saying?

PCHA_CHAR_ID = {"=Count(DISTINCT PCHA_CHAR_ID=PCGA_CHAR_ID) > 1"}

I don't think I understand the issue to be able to respond you with the correct expression.

Re: result of avg() dimension differs from expectation

citizenk — Mon, 04 Feb 2019 12:00:56 GMT

Hi Sunny,

just to inform you: The passage you mentioned caused more trouble than it helped so I overworked my data model to get rid of that calculation.
Thereby I was able to exchange

PCHA_CHAR_ID = {"=Count(DISTINCT PCHA_CHAR_ID=PCGA_CHAR_ID) > 1"}

to the much more useable PCGA_PROP = {1} and it now works fine in that particular chart.

=num(Aggr(
Avg({<PCHA_PUBL = {1}, PCGA_PROP={1}>} Aggr(

(Count({$<PCHA_VALUE_GB-={'-'}>}distinct PBGA_BG_NO) + Count({$<PCHA_IRRELEVANT= {1}>}distinct PBGA_BG_NO))
/
max(aggr(nodistinct Count(distinct PBGA_BG_NO), PAGA_GA_ID))

, PCHA_CHAR_ID, PAGA_GA_ID, PCHA_CHAR_GB))
, PAGA_GA_ID), '##0 %')

Re: result of avg() dimension differs from expectation

sunny_talwar — Mon, 04 Feb 2019 12:18:27 GMT

Sounds good. I am glad you managed to resolved this all by yourself 🙂