Tips and Tricks: calculate number of days worked

Print Page | Close Window

calculate number of days worked

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7655
Printed Date: 03 May 2024 at 4:33am

Topic: calculate number of days worked

Posted By: RTSFO
Subject: calculate number of days worked
Date Posted: 10 Sep 2009 at 4:20pm

Hi

I am trying to calculate the number of days worked in a week.

I have 7 columns in the table with all the days onf the week. If that has a value it means that it needs to be counted.

Since it's a boolean value, I am not able to add it.

can anyone help?

Thanks

RDT

Replies:

Posted By: DBlank
Date Posted: 10 Sep 2009 at 5:43pm

(if table.field1=True then 1)

+

(if table.field2=True then 1)

+
(if table.field3=True then 1)

...

Posted By: RTSFO
Date Posted: 10 Sep 2009 at 6:04pm

I have this formula and still does not seem to work:

(if {SubjectData.column_name} = "work_sun"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_mon"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_tue"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_wed"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_thu"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_fri"

and {subjectData.value} = "Y" then 1)

+

(if {SubjectData.column_name} = "work_sat"

and {subjectData.value} = "Y" then 1)

Thank you

Posted By: DBlank
Date Posted: 10 Sep 2009 at 6:56pm

I was under the impression these fields were all on the same row. Your second post implies each day is on its each data row.

If that is tha case and each data row is a day likely you only need one formula:

if {subjectdata.value}="Y" then 1

From there it is more of an issue of your design (how you made all of these appear on one row) for how to sum this fomrula field to get one week of days.

Is this closer to what is going on or get you on a track you can use?

Posted By: RTSFO
Date Posted: 10 Sep 2009 at 9:54pm

I still don't understand it though. What I am trying to do is count the value is Y for however many days for one person.

Thanks

Posted By: DBlank
Date Posted: 11 Sep 2009 at 6:50am

1. am I correct that you have one row of data per day per person?

if you create a formula field using the formula I gave you (name it "Present" for this example):

if {subjectdata.value}="Y" then 1

this will give you a field that has a 1 when present and a 0 when absent.

The { @Present} formula field can be used in summary formula or a crosstab to give you Sums. I do not know how you have your report set up for grouping to tell you exactly how to do this.

Example if you group on employee you can do a SUM( mailto:%7b@Present - {@Present }, employeegroup) and palce this on the group footer it we will count all of the present days per employee.

Make sense?

Posted By: RTSFO
Date Posted: 11 Sep 2009 at 9:00am

That's exactly what I did. I have a formula for everyday. day_1, day_2 etc. And then I created another formula where I am adding the values for all seven days. But it still shows up as 0.

mailto:%7b@day_1%7d+%7b@day_2 - {@day_1}+{@day_2 } etc

Thanks

Posted By: DBlank
Date Posted: 11 Sep 2009 at 9:06am

Lets get back to basics on this because I am lost as to your report set up.

with no grouping, just place the column_name field and the subject_data value and 1 formula field (defined as if {subjectdata.value}="Y" then 1) onto the detail section and run the report. You should see something like

work_thu Y 1

work_fri N 0

work_thu N 0

etc...

Correct? If not, what do you see?

Posted By: RTSFO
Date Posted: 11 Sep 2009 at 11:04am

I still see zeros.

Sorry to take so much of your time.

Posted By: DBlank
Date Posted: 11 Sep 2009 at 11:07am

What exactly do you see for the subject_data field?

or post the first few rows of the data as it appears...

Print Page | Close Window