How to get average count of employees for past 3 months

[#1]

Hi,

My current report takes in the date input from user to compute the number of employees under each industry. It checks for employees that passed their 3 months probation period.

"LocalCount" -> count(if(AddMonths("Employment Date",3)<=vCurrentDate,LocalEEno))

My question is how can i show in the report the average localcount for the past 3 months of each industry based on whatever CurrentDate the user enter?

I have attached my report for your reference. Thanks!!

Regards

Jiawei

[spsrk_84]

Hi,

USe this function

Avg(count(if(AddMonths("Employment Date",3)<=vCurrentDate,LocalEEno)))

if it won't works then divide the CountValue by 3 i.e CountValue/3 which gives the average Count of past three months.

Regards,

Ajay

[#3]

Dear ajay143,

The current report selection is based on user's entering date to the inputbox (vCurrentDate). How can i get report to compute to get count of employees under each industry for the past 3 months and average them?

For example, when user enter 30/04/10, there will be a field "Average Count" that gets sum total of employees under each industry for March,Feb and Jan, then average it.

Regards

Jiawei

[#4]

Does this expression works?

count(if(AddMonths("Employment Date",3)<=vCurrentDate-1,LocalEEno)) +

count(if(AddMonths("Employment Date",3)<=vCurrentDate-2,LocalEEno)) +

count(if(AddMonths("Employment Date",3)<=vCurrentDate-3,LocalEEno))

then / 3 ?

[spsrk_84]

In the above expression u have written whether the vCurrentDate-1 gives exactly the previous month?

I have written set expression like this Try this

(Count({$<AddMonths("Employment Date",3)={"<=" vCurrentDate},
MOnth(vCurrentDate)={MOnth(vCurrentDate)-1}>,LocalEEno)
+

Count({$<AddMonths("Employment Date",3)={"<=" vCurrentDate},
MOnth(vCurrentDate)={MOnth(vCurrentDate)-2}>,LocalEEno)
+
Count({$<AddMonths("Employment Date",3)={"<=" vCurrentDate},
MOnth(vCurrentDate)={MOnth(vCurrentDate)-3}>,LocalEEno)

) divide by 3

I need some clear information about your problem like

1) suppose Assume

Month	Joined Cnt	Probation Period Completes
Jun	50	Sep
Jul	60	Oct
Aug	70	Nov
Sep	80	Dec
Oct	90	Jan
Nov	40	Feb
Dec	20	Mar
Jan	45	Apr
Feb	50	May
Mar	40	Jun
Apr	90	Jul

In the Above Case if user enters Date like 29-04-2010 then ,when we say average u mean the

(

Count(EmpCount) who completed probation in the Month of March+

Count(EmpCount) who completed probation in the Month of Feb+

Count(EmpCount) who completed probation in the Month of Jan

) Divide by 3

The above logic is applied to resolve your problem,i f u feel i am not correct please explain the scenario wr.t to the above data set and get the result what is required then we can try to get the same result in expression.

I am using personal edition so it is not possible for me to see your Qvw file...