Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help calculating an average

Hi,

I have created the first 3 columns in the below straight table. This shows the '% Logged' by 'Category' and 'Week Ending'

The '% Logged' is calculated with the following expression.

=sum({$<[Category]=>} Count1)
/
count({$<[Category]=>} total<[Calendar.Date Week End]> [Call Start])

I now want to create a 4th column (as shown) which provides and average '% Logged' for each 'Category'

I've tried the below expression (and others!) but haven't been able to achieve the desired result... Can anyone help with the correct expression for this??

=aggr(
sum({$<[Category]=>} Count1)
/
sum({$<[Category]=>} total<[Calendar.Date Week End]> Count1)
,
[Category])

 

Attached is a sample app which may help

CategoryWeek Ending % Logged Average
TestA22/02/20152.06%2.77%
TestA01/03/20152.30%
TestA08/03/20152.52%
TestA15/03/20152.68%
TestA22/03/20152.37%
TestA29/03/20152.89%
TestA05/04/20153.86%
TestA12/04/20153.49%
TestB22/02/20152.36%3.12%
TestB01/03/20153.35%
TestB08/03/20153.10%
TestB15/03/20153.18%
TestB22/03/20152.87%
TestB29/03/20153.34%
TestB05/04/20153.02%
TestB12/04/20153.76%
TestC22/02/20152.41%2.46%
TestC01/03/20152.24%
TestC08/03/20152.78%
TestC15/03/20152.63%
TestC22/03/20152.33%
TestC29/03/20152.38%
TestC05/04/20152.30%
TestC12/04/20152.58%

Thanks

1 Solution

Accepted Solutions
tresesco
MVP
MVP

May be like this? PFA

View solution in original post

3 Replies
tresesco
MVP
MVP

May be like this? PFA

pokassov
Specialist
Specialist

Hello!


=avg(aggr(
sum({$<[Category]=>} Count1)
/
sum({$<[Category]=>} total<[Calendar.Date Week End]> Count1)
,
[Category]))

Not applicable
Author

Thanks a lot tresesco, you're reply got me on the right track. The expression I used in the end was

=bottom(
rangeavg(above(
sum({$<[Category 3/2]=>} Count1)
/
sum({$<
[Category 3/2]=>} total<[Calendar.Date Week End]> [Count1])
,0,rowno()))
)