Calculate average in a report

[naqsa]

Hi All,

I've built a report to identify the umber of days it takes for us to reimburse and employee's expenses. The report is pulling the employee name, employee location, date on which they submitted their expense report, date on which the report was paid, and the calculating number of days it takes to pay each report. Attaching sample output.

With this  data, I need to calculate the average number of days it takes reimburse an employee in each country during a given month.
- I am thinking about filtering the 'Date Paid' field to pull only those reports that were paid in the previous month.
- I need to figure out how to calculate the average number of days, per country, for each month. 
- Once this report is ready, I'm going to add it to a dashboard, which I have no training on so if you have any resources that you think will be helpful, please let me know.

Your help will be greatly appreciated.

[naqsa]

Attached report output.

[TziRi]

Firstly you can't calculate the avg number of days that takes the company to reimburse during a given month because you can see that are many report id that are reimburse in more than one month.
(1) You can calculate the avg time for the finished id reports in a month,but this is not the avg nb of days for than month,the month can have a ticket finished then and started with 3 months earlier .
(2) You can check when he starts let say 14.07 and ends at 05.08 and you want to make statistics for the month 7 you can take days from 14-31 but it is the problem that on 31 the ticket it isn't reimbursed. It will be like in the month 7 you will have a total of delay of 300 days but it will be splitted by 20 'half' tickets.


(1)First you make a new column where you extract the month  MONTH: to_char([Paid Date];'YYYYMM') then you create another column(query studio) and put there AVERAGE: average([#of days]for [Emp Country];[Month]) .You put in the report studio [Employee],[Emp Country],[MONTH],[AVERAGE]

(2) This is more complicated.
First you need to check for every month if the ticket started in that month and if did to check if it ends in the same month and calculate the number for that month.

if(month=monthPaidDate)
    { if(month=monthLastSubmitedDate)
         x=Day_LastSubmited-Day_PaidDate
       else
         x=31-Day_PaidDate      //  (nb of days in the month,i think it is a fct in cognos to know the days of the month)
     }
else
x=Day_LastSubmited

If your tickets last for 3 month you need also to check if there is at least one of them in the month and if not you add the number of days of the month.And then you do the same like in the first case (1).