Oracle Analytics and AI

Count on dates

Hi,

I like to calculate the count of joined employees, termination employees and the total employees from a given year and all months of that year. I have joining Date field and Termination field, I can not calculate joining and termination based on dates in a single analysis, moreover I can not find a solution to calculate total employees based on the year/month row.

I want like this,

Year	Month	Joined Employees	Terminated Employees	Total Employees
2013	Jan	20	3	100
	Feb	30	2	128
	Mar	50	0	178
	…..	10	1	187
	….	10	1	196
	Dec	30	2	224
Total		150	8	Current 224

Same to 2014, 2015 and 2016 (Jan)

Please help.

Find more posts tagged with

Accepted answers

All comments

choracy69

I think you can use Subtotal for Year/Month and Grand Total for all results.

[Deleted User]

+1 to using different totals. I've created an example here:http://slc02ojq.oracle.com:7780/analytics/saw.dll?Answers&path=%2Fshared%2FOTN%2FDifferent%20totals

Access with Prodney / Admin123 as username / password.

user10772118

Thank you Christian Berg for your reply and sample. I have seen he sample and I have few questions. (I am sorry for below very basic questions being very new to BI)

I have customized BI subject area. I don't have Time Subject Area in my subject area and I don't know know how to add standard "Time" subject area of Oracle BI. I assume if I have standard "Time" subject Area, I might able to solve the requirements. One of the requirement I have I need to calculate the joined and terminated employees on the same table. For that, I am using the following formulas.

1) year("Employee Info"."Join Date")

2) month("Employee Info"."Join Date")

3) count("Employee Info"."Join Date")

The above three give me the right result of joining, However, when I add the 4th field which is count("Employee Info"."Termination Date), this does NOT give me the right result this is because my year and month is based on the Join Date and NOT on termination date.

4) count("Employee Info"."Termination Date) This does not help me,

Therefore, I had to create another Analysis on "Termination Date" as following;

1) year("Employee Info"."Actual Termination Date")

2) Month("Employee Info"."Actual Termination Date")

3) Count ("Employee Info"."Actual Termination Date")

The above analysis again give the right result. for terminated employees

Now, my issue is how can I make the above two analysis in one analysis and how to add the Time Subject Area in my subject Area, it might solve the issue with Time Subject Area. Moreover, I am unable to count the employees on the each row of year and month. I mean I want to show on each year/month what was the count of the employees.

Please help.

Thanks.

[Deleted User]

You need a time dimension in your RPD business model which should be based off of a time dimension table. You can deduce one from the dates in your facts but then you'll have holes for the days where there's no fact entry!

Have a look at some of thses posts first and when questions persist please open a new thread since it's a different question then.

OBIEE 10G/11G - How to set up the time dimension (for time series functions Ago, Todate, ...) ? [Gerardnico]

OBI EE, Time Dimensions and Time-Series Calculations - Rittman Mead Consulting