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: 
Not applicable

Calculation of average in straight table

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_nameBench_DataMonth_Date
John Smith1.00Jan-15
John Smith1.00Feb-15
John Smith1.00Mar-15
John Smith1.00Apr-15
John Smith0.72May-15
John Smith0.75Jun-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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Contributor III
Contributor III

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

Not applicable
Author

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
Specialist II
Specialist II

try like this

=SUM(Bench_Data)/COUNT(DISTINCT Month_Date)

or

=aggr(Avg(Bench_Data),Month_Date)

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

it doesnt work

Not applicable
Author

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