- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Identify DayOfWeek within date range

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Identify DayOfWeek within date range

Former Member

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 2007 May 18 11:55 PM

56

- SAP Managed Tags:
- SAP Crystal Reports

Post Author: mcatd

CA Forum: Formula

I need to write a report counting employee absences for each day within a user-selected week. The employee-absence records I have to work with look more or less like this:

Employee Name Absence Type Abs Start Date Abs End DateSmith S 02/14/07 02/15/07Jackson V 02/12/07 02/16/07Tulowitzki S 02/15/07 02/16/07

The report needs to look like this:

DayOfWeek Sick VacationMonday 0 1Tuesday 0 1Wednesday 1 1Thursday 2 1Friday 1 1

I am completely stumped on how to assign a DayOfWeek value to those dates that are only implicit in the range, particularly as the size of the range varies from one record to the next.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2007 May 24
8:13 PM

Post Author: Charliy

CA Forum: Formula

Oh, and the Running Total should be doing a Distinct Count of a formula that is the Employee (Client. Patient, whatever) ID appended to the Date.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2007 May 24
6:23 PM

Post Author: Charliy

CA Forum: Formula

I'll give you an example of the evaluate formula, all the others would be a variation from this.

We'll call this Running Total SickMon It's counting Sick on Mondays.

Since you're going to be using the Day of Week so many times, let's say you took a shortcut and made a formula so you only do it once.

Now the evaluate formula for SickMon will look something like:

{@DayOfWeek} = 2 and {table.field} = "Sick"

You'd have a similar Running Total called VacThur with an evaluate formula like:

{@DayOfWeek} = 4 and {table.field} = "Vacation"

and so on for Holidays, Comp Time, whatever else you've got

Then you just line them all up in the report footer.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2007 May 24
5:48 PM

Post Author: mcatd

CA Forum: Formula

Charliy, can you give me a little more detail about what that solution would look like and how it would work?

I'm afraid there are actually TWELVE different absence codes - I simplified it to sick and vacation just for the sake of the example - so I'm looking for a more streamlined solution, if possible.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2007 May 21
3:49 PM

Post Author: Charliy

CA Forum: Formula

You could also use ten Running Totals, each with an evaluate formula for a specific day of week and sick/vacation.

Former Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2007 May 19
1:55 AM

Post Author: SKodidine

CA Forum: Formula

This is a bit messy and cumbersome but works. Perhaps others have a better idea.

Create all these formulae:

processdates (Place this formula in your details section and suppress it)

numbervar sunsick;

numbervar sunvac;

numbervar monsick;

numbervar monvac;

numbervar tuesick;

numbervar tuevac;

numbervar wedsick;

numbervar wedvac;

numbervar thusick;

numbervar thuvac;

numbervar frisick;

numbervar frivac;

numbervar satsick;

numbervar satvac;

//

datevar startdate := date(2007,02,12); // abs start date

datevar enddate := date(2007,02,16); // abs end date

stringvar type := 'v'; // type

//

datevar pdate := startdate;

//

while pdate <= enddate do

(

select dayofweek(pdate)

case 1:

(if type = 's' then

sunsick := sunsick + 1

else if type = 'v' then

sunvac := sunvac + 1;)

case 2:

(if type = 's' then

monsick := monsick + 1

else if type = 'v' then

monvac := monvac + 1;)

case 3:

(if type = 's' then

tuesick := tuesick + 1

else if type = 'v' then

tuevac := tuevac + 1;)

case 4:

(if type = 's' then

wedsick := wedsick + 1

else if type = 'v' then

wedvac := wedvac + 1;)

case 5:

(if type = 's' then

thusick := thusick + 1

else if type = 'v' then

thuvac := thuvac + 1;)

case 6:

(if type = 's' then

frisick := frisick + 1

else if type = 'v' then

frivac := frivac + 1;)

case 7:

(if type = 's' then

satsick := satsick + 1

else if type = 'v' then

satvac := satvac + 1);

pdate := pdate + 1;

);

monsick

whileprintingrecords;

numbervar monsick;

totext(monsick,0);

tuesick

whileprintingrecords;

numbervar tuesick;

totext(tuesick,0);

wedsick

whileprintingrecords;

numbervar wedsick;

totext(wedsick,0);

thusick

whileprintingrecords;

numbervar thusick;

totext(thusick,0);

frisick

whileprintingrecords;

numbervar frisick;

totext(frisick,0);

monvac

whileprintingrecords;

numbervar monvac;

totext(monvac,0);

tuevac

whileprintingrecords;

numbervar tuevac;

totext(tuevac,0);

wedvac

whileprintingrecords;

numbervar wedvac;

totext(wedvac,0);

thuvac

whileprintingrecords;

numbervar thuvac;

totext(thuvac,0);

frivac

whileprintingrecords;

numbervar frivac;

totext(frivac,0);

In your report footer, create text objects for 3 headings such as "Day of Week", "Sick", "Vacation". Under "Day of Week", create 5 text objects containing "Monday", "Tuesday", "Wednesday", "Thursday", "Friday". Place the appropriate formulae from above next to the appropriate day.

I tested it and it seems to work, so give it a shot.

Ask a Question

Related Content

- Creating a Functional Specification Document (FSD) in SAP: A Comprehensive Guide in Technology Blogs by Members
- Series on Workload Analysis for HANA Platform - 6. HANA Workload Management Methodology in Technology Blogs by SAP
- Series on Workload Analysis for HANA Platform - 5. Performance Impact by HANA Internal Functions in Technology Blogs by SAP
- Series on Workload Analysis for HANA Platform - 4. Workload from Network, I/O and Locks in Technology Blogs by SAP
- SAP ABAP in Technology Blogs by Members

Top Q&A Solution Author

User | Count |
---|---|

70 | |

10 | |

10 | |

7 | |

6 | |

6 | |

6 | |

5 | |

5 | |

5 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.