topic Re: Calculation of average in straight table in QlikView

Calculation of average in straight table

Fri, 31 Jul 2015 12:27:33 GMT

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?

Re: Calculation of average in straight table

Gysbert_Wassenaar — Fri, 31 Jul 2015 12:31:21 GMT

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.

Re: Calculation of average in straight table

Fri, 31 Jul 2015 12:33:36 GMT

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"

Re: Calculation of average in straight table

trpatel123 — Fri, 31 Jul 2015 12:34:24 GMT

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

Re: Calculation of average in straight table

Fri, 31 Jul 2015 12:37:15 GMT

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

Re: Calculation of average in straight table

rubenmarin — Fri, 31 Jul 2015 12:40:25 GMT

Hi Diana, maybe:

Sum(Bench_Data)/GetPossibleCount(Month_Date)

Sum(Bench_Data)/COUNT(TOTAL DISTINCT Month_Date)

Re: Calculation of average in straight table

sunilkumarqv — Fri, 31 Jul 2015 12:41:34 GMT

try like this

=SUM(Bench_Data)/COUNT(DISTINCT Month_Date)

=aggr(Avg(Bench_Data),Month_Date)

Re: Calculation of average in straight table

Gysbert_Wassenaar — Fri, 31 Jul 2015 12:45:44 GMT

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

Re: Calculation of average in straight table

Fri, 31 Jul 2015 12:47:59 GMT

it doesnt work

Re: Calculation of average in straight table

Fri, 31 Jul 2015 12:55:45 GMT

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