Changing Week Beginning Date to Week Ending Date? 1

[betterdays1]

I'm grouping a date field by week. My problem is that Crystal defaults to using a week beginning date for the group header field. I need this to show week ending date. I know there must be an easy way to do this but I haven't yet figured it out.

 

[synapsevampire]

What Crystal does is show the first date that you have data for, for the given week, not the beginning week date.

Place the field in the details section and right click->Insert Summary->Maximum.

Drag the field to the Group Header. Again, this isn't the ending date for the week, it's the last date that your data has for that week.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mbarron]

Try this formula to show you a week ending of Saturday instead of starting of Sunday.

cdate(groupname({field.being.grouped},"weekly&quot+6

Mike
If you're not part of the solution, you're part of the precipitate.

 

[cm1234]

Hi everybody
CR 8.5

I need to get a report to start with first day off week
on a monday instead off sunday as in CR,
can anybody help me.

best regards
cm1234

 

[mbarron]

Create the following formula, then do your grouping on the formula field.

datevar adjusted;
if dayofweek({your.date.field})=1 then adjusted:=date({your.date.field})-6 else
adjusted:=date({your.date.field}-dayofweek({your.date.field}))+2 Mike
If you're not part of the solution, you're part of the precipitate.

 

[cm1234]

Ups!!

Sorry betterdays1
i simply wrote my problem in youre
thread by accident, once again sorry

best regards
cm1234

 

[pelajhia]

I have been thinking/needing this solution for about a week. I need to adjust to a Friday-Thursday week, which I have done successfully thanks to your formula, but I guess I don't completely understand what this formula is doing. Maybe you could shed some additional light?

Here is what I am using and it seems to work...

datevar adjusted;
if dayofweek({CUSTPROB.ADDDTTM})=1 then adjusted:=date({CUSTPROB.ADDDTTM})-0 else
adjusted:=date({CUSTPROB.ADDDTTM}-dayofweek({CUSTPROB.ADDDTTM}))+6 ;
I then convert ToText in order to group.

Just looking at the first part of the if, my understanding of this is: if your date is sunday, subtract 6, this will give you Monday of the previous week.
When I tried to translate this to getting Friday of the previous week, I used -2, but this in fact gave me Wednesday, so I subtracted a couple less 'days' and this gives me friday. (I know -0 looks really stupid, I will probably take it out after you chide me about it...) What is the formula really doing?
Thanks for any help!

 

[pelajhia]

ps - that last note should have been addressed to mbarron, but any help from anyone would be appreciated!

 

[mbarron]

You formula is saying:

if the DOW (day of week) is Sunday then the result is the same day. You can take out the "-0" For any other day go back in days, the number of the DOW (ie Tuesday=3) to give you the previous Sunday and add 6 days from ther giving you Friday


if dayofweek({CUSTPROB.ADDDTTM})=1 then adjusted:=date({CUSTPROB.ADDDTTM})-0 else
adjusted:=date({CUSTPROB.ADDDTTM}-dayofweek({CUSTPROB.ADDDTTM}))+6 ;


To get a true Friday thru Thursday week (Jan 3 to Jan 9 for example) use:

datevar adjusted;
if dayofweek({@date})in [1 to 5] then adjusted:={@date}-dayofweek({@date})-1
else
adjusted:={@date}-dayofweek({@date})+6




Mike
If you're not part of the solution, you're part of the precipitate.

 

[pelajhia]

ahhh! thanks!!