Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Formula creation for date selection Post Reply Post New Topic
Author Message
El22
Newbie
Newbie
Avatar

Joined: 07 Apr 2011
Online Status: Offline
Posts: 10
Quote El22 Replybullet Topic: Formula creation for date selection
     Posted: 02 May 2011 at 11:44pm
Hello,
 
I need to use the selection expert to select a date, but its quite a specific rule I am looking to create.
 
I need to select records from instances which happened 20 days ago, but at any point during that Monday to Friday week, for example:
 
If I run the report on todays date (03/05/11), 20 days ago would be 13/04/2011, however I want to look at every instance in this working week, which would be 11/04/2011-15/04/2011.
 
How could I create a formula for this, which would identify the week and work regardless of which date I ran the report?
 
Any help would be brilliant.
 
Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 May 2011 at 4:25am
make a date param as your start date and include a weekday() function in your select statement
 
table.date > {?dateparam} and
dayofweek(table.date) in 2 to 6
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 May 2011 at 7:50am
I think I misunderstood your post as you wanting only monday-friday dates that have happened since a date entered by a user.
After rerading it I know think what you want are the dates for the monday and friday of the week that was 20 days ago.
maybe this...
table.date in
dateadd('d',-20 -(dayofweek(dateadd('d',-20,currentdate)))+2,currentdate)
to
dateadd('d',-20 -(dayofweek(dateadd('d',-20,currentdate)))+6,currentdate)
 


Edited by DBlank - 03 May 2011 at 7:51am
IP IP Logged
El22
Newbie
Newbie
Avatar

Joined: 07 Apr 2011
Online Status: Offline
Posts: 10
Quote El22 Replybullet Posted: 03 May 2011 at 9:34pm
Hello,
 
Thanks for the responses.
 
What I am looking for is to be able to say "this (week)day was 20 days ago, therefore I want to look at every instance which happened in this week and select all these instances". For example:
 
If I were to run the report today, 20 days ago would be 14/04/2011. I would like to look at all sales from Monday 11th to Friday 15th.
 
I think you were right with a date parameter, I will play around with this.
 
Thanks
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.