Group Every 2 Weeks 1

[ternas1]

I'm using CR 2008, and I am trying to create a formula that groups data every 2 weeks and display a label with the last day of that time period.
Periods begin on a Sunday and end on a Saturday 14 days later.
I would like to do this without creating a separate table that lists 2 week periods for the next 10 years.

I am trying to use this formula as a group set "for each two weeks": date({table.date} - dayofweek({table.date})+14).

I tried a time period of 7/4/10 - 7/17/10. Instead of showing 7/17/10 (the last day of the the period), it displayed the Sunday before the last day, 7/11/10.

I can't figure out why it won't display the last date. Any ideas on how to correct this?

 

[lbass]

Try the following as a formula to group on:

if remainder(datepart("ww",{table.date}),2) = 0 then
{table.date}-dayofweek({table.date})+7 else
{table.date}-dayofweek({table.date})+14

This will create groups that are two weeks in length with the groupname showing the ending Saturday. However, if the two intervals using this formula are off by a week compared to your requirements, just change the "=" to "<>".

-LB

 

[ternas1]

After changing it to "<>" it worked. I wasn't familiar with the remainder function, but it seems to work great in designating every 2nd week.

Thank you very much.

 

[Tuch]

I tried using the formula you suggested for ternas1, however, it's giving me an error : The formula result must be a string. I tried using ToText and nothing seems to be working. Could you please help. Thanks.

 

[lbass]

Please post the formula you are using and explain where you entered this formula.

-LB

 

[Tuch]

Sorry, first time posting to Tek-Tips. I read the post all the time for help.

Here's the formula I used in Change Group Options:

if remainder(datepart("ww",{VP_ACCRUALTRANV42.EFFECTIVEDATE}),2) = 0 then
{VP_ACCRUALTRANV42.EFFECTIVEDATE}-dayofweek({VP_ACCRUALTRANV42.EFFECTIVEDATE})+7 else
{VP_ACCRUALTRANV42.EFFECTIVEDATE}-dayofweek({VP_ACCRUALTRANV42.EFFECTIVEDATE})+14

Here is what I am trying to do. I have a Crystal report that is grouped by Position, Fullname and then Effective date. The Effective date is grouping every two weeks, however, it's starting off on the wrong week. This is a payroll report. Let me know if you need more info. Thanks for your help.

 

[lbass]

You are putting the formula in the wrong place. You should be creating the formula in the field explorer->formulas->new and then inserting a group on the formula directly.

-LB

 

[Tuch]

Forgive me for not knowing this...how does my data in the detail section change to the correct pay cycle. It's still off a week.

 

[Tuch]

Ok, I actually fixed it ! Yeah. Thanks again.