Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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).

1 Solution

Accepted Solutions
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

View solution in original post

17 Replies
vinieme12
Champion III
Champion III

try

Num(

                 Avg( TOTAL

                                Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

                                ),

                 '# ##0,000%')



or



Num(

                 Avg(

                                Aggr(NODISTINCT  (1-Sum(Fact1)/Sum(Fact2)),Date)

                                ),

                 '# ##0,000%')

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
aisolomatin
Contributor III
Contributor III
Author

Vineeth, thanx for your reply, but it still doesn't fit (same values for C1 and C2).

f2.png

sunny_talwar

This one should, I think

Num(

                 Avg( TOTAL

                                Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

                                ),

                 '# ##0,000%')

vinieme12
Champion III
Champion III

try below


Num(

                 Avg( TOTAL

                                Aggr(1-Sum(Fact1)/Sum(Fact2),Date)

                                ),

                 '# ##0,000%')

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
aisolomatin
Contributor III
Contributor III
Author

Hi,Sunny! Nope, still doesn't fit:

f3.png

 

Channal

Date

Fact1

Fact2

C1

01.01.2017

10

100

C1

02.01.2017

8

70

C2

01.01.2017

3

10

C2

02.01.2017

5

9

 

aisolomatin
Contributor III
Contributor III
Author

Nope, doesn't work (see previous answer, please).

sunny_talwar

Why add <Channel>?

This worked for me

Avg(TOTAL Aggr(1-Sum(Fact1)/Sum(Fact2), Date))


Capture.PNG

sunny_talwar

I guess what is the expected output here?

aisolomatin
Contributor III
Contributor III
Author

Sunny,

I expect to see that table:

 

Channal

Value

Total

85,863%

C1

88,182%

C2

83,544%