Average Service Years calculation with mutiple record in Bex 3.5 Report

[Former Member]

Hi Experts,

I have a report requirement were i am not getting the desired result. Most of the time i search through forum and find my way. But this time i am having a tough time finding solution.

Here is the requirement.

I have to create a report to display the average service years. Sample report

Org Unit Employee Education No of Employee Service Years Average Service Years

20002133 10050981 Bachelors 1 40 40

10050981 Masters 1 40 40

20002134 10034567 Bachelors 1 22 22

10034567 Masters 1 22 22

10034567 Technical 1 22 22

-

---

2 62 31 (desired result )

I am using a simple formula (Total Service years / no of employee ). However i am not getting the desired result. In my case, the service years is getting added i.e (40+ 40 +22 + 22 + 22 = 146 ) and then gets divided by 2 as a result i get 73 as the average service years.

We are using BW 3.5 Query Designer Which have very limited functionality.

So, Guys if anyone have a solution or any suggestions to work on this? Any help greatly appreciated..

Thanks Guys !!

Jhansi

Edited by: Jkarupa on May 19, 2011 9:02 PM

[former_member183777]

If you want to see the average in the sub total rows only, you can just set the calculate results as to average of all values on the service years KF. You won't even need the formula.

[Former Member]

Hi Vineet, Thanks for the response. I tried using Average all values, It didn't help me though. I guess i haven't explain it well. The problem in my case is , we are using an infoset to bring data from cube and dso's.

we have a keyfigure for ' service yrs'. now if an employee A is having 3 records , the keyfigure ' service yrs ' aggregates the data before being used by the query. For ex - If an employee A have service yr as 10, then coz of 3 records for the same employee, the keyfigure stores it as 10 + 10 + 10 = 30 and If another employee B have service yrs as 40, then the key figure holds 40 + 40 = 80 for that employee.

Now if i use this keyfigure to calculate average using ' Average All Values ' in query , I get overall result something like this i.e

(30 + 80 )/ 5 = 22 yrs. Where as the actual overall result should be 10 + 40 = 50 / 2 = 25 (expected output ).

My report have the reqm to display the headcount , multiple record for the employee as welll as average serv years.

Hope i am clear.

Pls let me know if you have any solution for this.

Thanks,

Jhansi

[Former Member]

Experts,

Any suggestions on this?

I am looking for some help in Bex Designer side as i dont want to do any coding to bring result. I am looking for an alternate to Exception aggregation ( Total ) function in BW 3.5 .

Thanks