Calculation of average in straight table

[#1]

Hello, guys!

I have a problem with avg() function in my pivot table. It doesnt seem to work correctly.

I need to calculate average of values for the months picked in filter, for example from January till July.

Here's the sample information of  employee:

Emp_name	Bench_Data	Month_Date
John Smith	1.00	Jan-15
John Smith	1.00	Feb-15
John Smith	1.00	Mar-15
John Smith	1.00	Apr-15
John Smith	0.72	May-15
John Smith	0.75	Jun-15

As you see there's no value in July, so instead of average = 0,78, I get 0,91 in my table. Thus, it counts average of months that has  values,  in this example from January till June, and I want it to count from January till July.

How can I fix it, guys?

[Gysbert_Wassenaar]

How should Qlikview know that you want to count from january till july and not for example from january till october?

Perhaps sum(Bench_Data)/num(Month(Today())) does what you want.

[#3]

I have an expression in my table Avg(Bench_Data). And whenever I want to see average for a distinct period of time I pick months in the filter named "Months"

[trpatel123]

=SUM(Bench_Data)/COUNT({1}TOTAL DISTINCT Month_Date)

[#5]

I dont want to count the average for entire year, I want to count the average picked in the filter

[rubenmarin]

Hi Diana, maybe:

Sum(Bench_Data)/GetPossibleCount(Month_Date)

or

Sum(Bench_Data)/COUNT(TOTAL DISTINCT Month_Date)

[sunilkumarqv]

try like this

=SUM(Bench_Data)/COUNT(DISTINCT Month_Date)

or

=aggr(Avg(Bench_Data),Month_Date)

[Gysbert_Wassenaar · Accepted solution]

Make sure your Month_Date field contains date values, not string values. Then you can select the months and calculate the number of months with Month(max(Month_Date)) and Month(Min(Month_Date)) and subtract these two values, for example in a variable: vNoOfMonths: =1+Month(max(Month_Date))-Month(Min(Month_Date)).

Your expression could then be something like sum(Bench_Data)/$(vNoOfMonths).

[#9]

it doesnt work

[#10]

Bingooooo!!! That works perfectly! Thank you for your smart idea!!