Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor III

## Aggr & avg

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

17 Replies
MVP

Date is not a dimension? or you want this?

Contributor III
Author

Yes, I don't want Date as dimension.

MVP

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?

Contributor III
Author

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%

MVP

Here you are

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

Contributor III
Author

Magic! Thank you!

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

MVP
Contributor III

Hi can someone help me with this question ?

avg(aggr())

Anna

Community Browser