LastFullWeek function in CR

[gennaroalpha7]

Hi, I am relatively new to CR and I am looking for help. I have a rpt that I automatically run on Mondays, but I would like to just capture the data from the week before i,e., Monday - Sat. Can someone please give me the complete code and instruct me as to where I add it in CR.

Thank you.
gennaroalpha7

 

[zigstick]

{YourDateField} in Last7Days
This will give you the last 7 days from the time the report is run.
Also useful is LastFullMonth, MonthToDate, YearToDate. There are others as well.

 

[CoSpringsGuy]

{yourdatefield} in [(currentdate -7)-(dayofweek(currentdate)-2)to (currentdate -7)+(7 - dayofweek(currentdate - 6))]

you can use this any day of the week to get the previous Mon - Friday ... slight modification if you need Sun - Saturday

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Thank you for you answers...in the {yourdatefield) what do I put in there? do I add the current date (11/28/2012}? Is this the correct format? And where do I add the code?

Thanks.
gennaroalpha7

 

[CoSpringsGuy]

sorry ..

Copy and paste what I sent you in the record selection formula .. (Report - selection formula - record)

replace {yourdatefield} with the date field from your database

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Thank you. I will try that. Thanks for your help!

 

[gennaroalpha7]

Hi, here is the code that copied and pasted....after the "in" word I am getting a message that "A string is required here". Is there something missing?

{Calllog.RecvdDate} in [(currentdate -7)-(dayofweek(currentdate)-2)to (currentdate -7)+(7 - dayofweek(currentdate - 6))]



Thanks.
gennaroalpha7

 

[CoSpringsGuy]

that would usually indicate the data in {Calllog.RecvdDate} is not saved as a date field ... post a sample of what that data looks like



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

that field shows the date of the data...i.e., 2012-10-20 and other dates follow that

 

[CoSpringsGuy]

other dates as follows? does that mean there is more than just one date in the field? Or other data in the field?

If it only contains the data you said above then create a new formula called ctDate

date(
tonumber(split({@testdate},"-")[1])
,
tonumber(split({@testdate},"-")[2])
,
tonumber(split({@testdate},"-")[3])
)
use
{@ctDate} as the date in the formula i sent for reocrd selection


{@ctDate} in [(currentdate -7)-(dayofweek(currentdate)-2)to (currentdate -7)+(7 - dayofweek(currentdate - 6))]

Note: if you have a large database this will probably slow down your report a bit... if its smaller you may not even notice

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

whups .. that was frm my testing .. your formula shoudl read
date(
tonumber(split({Calllog.RecvdDate} ,"-")[1])
,
tonumber(split({Calllog.RecvdDate} ,"-")[2])
,
tonumber(split({Calllog.RecvdDate} ,"-")[3])
)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Hi, the {Calllog.RecvdDate} field is the date of the incident, not todays date...

 

[CoSpringsGuy]

and you want the past full week of incidents correct?

then that should work

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

geez i am recreating the wheel ... I cpmpletely forgot about the function that YOU mentioned in your post LOL

create the formula i gave you to fix your date ...

the record selection should then read

{@ctDate} in lastfullweek

that will however give you saturday and sunday

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Yes, under that field there are multiple incidents, each with it's own date.

which is the code, the fist one or the revised one?

I added the code under formula workshop - selection formulas - record selection...

 

[CoSpringsGuy]

ok im gonna start over because I have been all around the board ..

first create a formula in formula workshop.. right click formula fields and select new and name it crDate.. inter the following code and save it
date(
tonumber(split({Calllog.RecvdDate} ,"-")[1])
,
tonumber(split({Calllog.RecvdDate} ,"-")[2])
,
tonumber(split({Calllog.RecvdDate} ,"-")[3])
)

then go to repport - selection formula - record and enter
{@crDate} in lastfullweek

then run the report
that will give you Sunday through Saturday of the previous week..


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Hi, when I add this {@ctDate} in lastfullweek


It still says I "a string is required here". did i miss something? thanks for your patients.

 

[CoSpringsGuy]

not sure if thats a type but it chould be {@crDate} you have {@ctDate}

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

my typing is horrendous today... i shold look at spelling before I submit post!



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[gennaroalpha7]

Thank you very much for your help...it worked like a charm.

Is there a book you can recommend that I can pick up to learn about CR formula generation?

Thanks.
gennaroalpha7