Print Page | Close Window

Formula for selecting Weekdays and Weekends

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13957
Printed Date: 02 May 2024 at 6:25pm


Topic: Formula for selecting Weekdays and Weekends
Posted By: KennyH
Subject: Formula for selecting Weekdays and Weekends
Date Posted: 02 Aug 2011 at 4:52pm
All,
 
I am trying to create a query that returns a results set of weekday transactions only.  I am unsuccessfully using the DayofWeek  function as follows:
//If {Trips.Trip Date} >= Date (2011,07,01) and {Trips.Trip Date} <= Date (2012,06,30)
//and (DayofWeek({Trips.Trip Date},1) = 2
//or   DayofWeek({Trips.Trip Date},1) = 3
//or   DayofWeek({Trips.Trip Date},1) = 4
//or   DayofWeek({Trips.Trip Date},1) = 5
//or   DayofWeek({Trips.Trip Date},1) = 6)
// then
// average {Trips.Passengers})
// else
However, I believe, only the number of weekdays since July 1, 2011 is returned.  My hope is to get the correct syntax to bring back only weekdays of passengers.  Can some one please help?


-------------
KwH


Replies:
Posted By: hilfy
Date Posted: 03 Aug 2011 at 3:24am

Instead of using the "average" function I would do this in three formulas - one to count the number of passengers and another to count the number of week days.  It would look something like this:

{@IsWeekDay}
If DayOfWeek({Trips.Trip Date}, 1) > 1 and DayOfWeek({Trips.Trip Date}, 1) < 7 then 1 else 0
 
mailto:%7b@WeekDayPassengers - {@WeekDayPassengers }
if DayOfWeek({Trips.Trip Date}, 1) > 1 and DayOfWeek({Trips.Trip Date}, 1) < 7 then {Trips.Passengers} else 0
 
mailto:%7b@AvgWeekdayPassengers - {@AvgWeekdayPassengers }
if sum( mailto:%7b@IsWeekDay - {@IsWeekDay }) > 0 then
  sum({@WeekDayPassengers})/sum( mailto:%7b@IsWeekDay - {@IsWeekDay })
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window