result of avg() dimension differs from expectation

[citizenk]

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.

[sunny_talwar]

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?

[citizenk]

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

[sunny_talwar · Accepted solution]

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

[citizenk]

Thank you. Both work perfectly.

[citizenk]

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:

[sunny_talwar]

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

[citizenk]

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

[sunny_talwar]

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

[sunny_talwar]

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)