Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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).
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.
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"
=SUM(Bench_Data)/COUNT({1}TOTAL DISTINCT Month_Date)
I dont want to count the average for entire year, I want to count the average picked in the filter
Hi Diana, maybe:
Sum(Bench_Data)/GetPossibleCount(Month_Date)
or
Sum(Bench_Data)/COUNT(TOTAL DISTINCT Month_Date)
try like this
=SUM(Bench_Data)/COUNT(DISTINCT Month_Date)
or
=aggr(Avg(Bench_Data),Month_Date)
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).
it doesnt work
Bingooooo!!! That works perfectly! Thank you for your smart idea!!