Aggr & avg

[aisolomatin]

Hi there!

Need your help so much with issue: I need to calculate Average by Dates with formula: 1-fact/fact2.

I've managed with Text Object, but I don't know what to do with table:

For text object I've used formula:

Num(
                 Avg(
                                Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
                                ),
                 '# ##0,000%')

But it doesn't fit for table (second expression in table).

I tried to add field "Channel" to Aggr expression, but still doesn't work properly (first expression in table).

[sunny_talwar]

Date is not a dimension? or you want this?

[aisolomatin]

Yes, I don't want Date as dimension.

[sunny_talwar]

Can you explain that why C1 gets the percentage that is associated with Date 01.01.2017 and C2 gets the percentage associated with Date 02.01.2017. Both have Fact1 and Fact2 for both the dates?

[aisolomatin]

Sunny,

my falt, you are right. Correct values:

Channel	Avg
C1	89,286%
C2	57,222%
Total	85,863%

Calculation for C1:

((1-10/100)+(1-8/70))/2=89,286%

С2:

((1-3/10)+(1-5/9))/2=57,222%

Total:

((1-(10+3)/(100+10)-(1-(8+5)/(70+9))/2 = 85,863%

[sunny_talwar · Accepted solution]

Here you are

If(Dimensionality() = 0, Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date)), Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date, Channal)))

[aisolomatin]

Magic! Thank you!

Can you pls give a few words about using Dimensionality()?

[sunny_talwar]

Read here

How to use - Dimensionality()

and here

The second dimension... or how to use secondarydimensionality()

[ascioneanna]

Hi can someone help me with this question ?

avg(aggr())

Anna