Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
aisolomatin
Contributor III
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:

f1.png

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
sunny_talwar

Date is not a dimension? or you want this?

Capture.PNG

aisolomatin
Contributor III
Contributor III
Author

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?

Capture.PNG

aisolomatin
Contributor III
Contributor III
Author

Sunny,

my falt, you are right. Correct values:

 

ChannelAvg
C189,286%
C257,222%
Total85,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

Here you are

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

Capture.PNG

aisolomatin
Contributor III
Contributor III
Author

Magic! Thank you!

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

ascioneanna
Contributor III
Contributor III

Hi can someone help me with this question ?

avg(aggr())

Anna