| Author |
Message |
bigwang916
Newbie
Joined: 21 May 2012
Location: United States
Online Status: Offline
Posts: 13
|
  
Topic: Calculate Date Diff + ? Help Please
Posted: 26 Sep 2013 at 8:36am |
|
Hi everyone.
I have 2 date fields {employee.startdate} and {employee.enddate} . I would like to create a crosstab to somehow capture a running total for all the each month count of employees who are "active". Is this possible?
For example:
|Jan | Feb | Mar
Count of Active Employees |10 | 8 | 9
Please let me know what they best approach would be.
Thank you!
|
 IP Logged |
|
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 26 Sep 2013 at 10:36am |
|
in selections expert putt
isnull({employee.enddate})
create a formula E_start_date
monthname(month({employee.startdate}))
create a crosstab
in columns place E_start_date
in summarized field place employee_ID(or name)
click under that on change summary select distinct count.
|
| IP Logged |
|
bigwang916
Newbie
Joined: 21 May 2012
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 26 Sep 2013 at 1:43pm |
|
Thanks for the reply.
I don't think that will display what I want. I would like it to count the employee "1" for every month being "active".
For example:
Joe started 01/26/2012 and ended 3/24/2012
John started 02/26/2012 and ended 3/24/2012
The cross-tab should look like this:
| Jan | Feb | Mar
Active employee count | 1 | 2 | 2
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 26 Sep 2013 at 1:59pm |
|
i dont think you can use crosstab for this report each month would need to created as a formula.
like
Jan
if ({employee.enddate} < = date(2012,1,31) or
isnull({employee.enddate}) ) and
{employee.fromdate} < = date(2012,1,31)
then employee_ID
than create a summary
distinctcount(jan)
Edited by kostya1122 - 26 Sep 2013 at 2:44pm
|
| IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 26 Sep 2013 at 9:32pm |
|
Does it have to be in the format
|Jan|Feb|Mar
Active employee count|1 |2 |2
Or can it be like
Active employee count
Jan: 1
Feb: 2
Mar: 3
Otherwise you could group your report on month, and create 1 formula. But I don't know if that is acceptable.
|
| IP Logged |
|
bigwang916
Newbie
Joined: 21 May 2012
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 27 Sep 2013 at 5:45am |
|
Thanks guys.
I think this format would also be ok
-----------
Active employee count
Jan: 1
Feb: 2
Mar: 3
------------
As long as when we run the report for 2-3 years, we can group or differentiate between the years. So maybe:
2012
Active employee count
Jan: 1
Feb: 2
Mar: 3
...
2013
Active employee count
Jan: 1
Feb: 2
Mar: 3
...
Thanks.
|
| IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 30 Sep 2013 at 12:02am |
|
You would have to make 2 grouping levels. First, group your report on date, and where it says "The section will be printed:", choose "for each year".
After that, I would suggest you make 2 formula's. The first one I called month, and looks like:
(if month({table.datefield}) in [1,2,3,4,5,6,7,8,9] then '0' & totext(month({table.datefield}),0,'')
else totext(month({table.datefield}),0,'')) & ': ' &
cstr(monthname(month({table.datefield})))
The second formula I named Month name, and looks like this:
cstr(monthname(month({table.datefield})))
You have to make a second level of grouping. Use the formula Month for this. Remove the field in your design mode, and replace it with the Month name formula.
What this is doing:
This formula Month is first checking if the month number is under 10. If it is, it is placing a 0 in front of it. This is important for the order, you don't want month 1, then 10, 11, 12, 2. This is giving you the months in the correct order.
However, I figure you don't want to show the month number in your actual report. The second formula is just giving you the month name. By switching the original GROUP 1 field with Month name, you just show the name.
The reason that you cannot group on Month name, is that you don't want the order to be alphabetical I assume.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Oct 2013 at 4:32am |
do you have a calendar table you can join to?
this would resolve your problems.
Otherwise I would caution you to recognize that any solution using a summary function or Running Total will require you to make one east one summary formula (Or RT) per month that you want to include in your report. Assuming this is along term use report you will also need to make sure that your formula's 'roll' with the current date.at l
|
| IP Logged |
|
|
|
