Crystal Reports calculate number of days worked

Tips and Tricks
Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks

Topic: calculate number of days worked

Author	Message
RTSFO Newbie Joined: 10 Sep 2009 Online Status: Offline Posts: 36	Topic: calculate number of days worked 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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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) ...
	IP Logged

RTSFO Newbie Joined: 10 Sep 2009 Online Status: Offline Posts: 36	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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? Edited by DBlank - 10 Sep 2009 at 6:58pm
	IP Logged

RTSFO Newbie Joined: 10 Sep 2009 Online Status: Offline Posts: 36	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
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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({@Present}, employeegroup) and palce this on the group footer it we will count all of the present days per employee. Make sense?
	IP Logged

RTSFO Newbie Joined: 10 Sep 2009 Online Status: Offline Posts: 36	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. {@day_1}+{@day_2} etc Thanks
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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? Edited by DBlank - 11 Sep 2009 at 9:07am
	IP Logged

RTSFO Newbie Joined: 10 Sep 2009 Online Status: Offline Posts: 36	Posted: 11 Sep 2009 at 11:04am
	I still see zeros. Sorry to take so much of your time.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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... Edited by DBlank - 11 Sep 2009 at 11:08am
	IP Logged

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.032 seconds.