topic Re: Average for category in QlikView

Average for category

Wed, 29 Apr 2015 13:34:59 GMT

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

Category	Week Ending	% Logged	Average
TestA	22/02/2015	2.06%	2.77%
TestA	01/03/2015	2.30%
TestA	08/03/2015	2.52%
TestA	15/03/2015	2.68%
TestA	22/03/2015	2.37%
TestA	29/03/2015	2.89%
TestA	05/04/2015	3.86%
TestA	12/04/2015	3.49%
TestB	22/02/2015	2.36%	3.12%
TestB	01/03/2015	3.35%
TestB	08/03/2015	3.10%
TestB	15/03/2015	3.18%
TestB	22/03/2015	2.87%
TestB	29/03/2015	3.34%
TestB	05/04/2015	3.02%
TestB	12/04/2015	3.76%
TestC	22/02/2015	2.41%	2.46%
TestC	01/03/2015	2.24%
TestC	08/03/2015	2.78%
TestC	15/03/2015	2.63%
TestC	22/03/2015	2.33%
TestC	29/03/2015	2.38%
TestC	05/04/2015	2.30%
TestC	12/04/2015	2.58%

Thanks

Re: Average for category

sunny_talwar — Wed, 29 Apr 2015 13:39:15 GMT

Try this maybe:

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

Re: Average for category

giakoum — Wed, 29 Apr 2015 13:47:56 GMT

a sample app would help

Re: Average for category

Wed, 29 Apr 2015 15:16:08 GMT

Thanks for looking sunindia, your expression returns values but they're unfortunately incorrect.

Re: Average for category

sunny_talwar — Wed, 29 Apr 2015 15:18:40 GMT

What values is it returning? Would you be able to share a sample?

Best,

Sunny

Re: Average for category

Wed, 29 Apr 2015 15:20:56 GMT

sample now attached.

Thanks