How to Create Weekly Groups starting Wednesday 2

[olushow]

I'm using CR10 and trying to create a line chart which has a weekly running total of tickets opened based on customer company. For Mondays through Sundays...--- 12/06, 12/13, so I can summarize the total number of tickets opened for all companies, on a weekly basis. How do I create the weekly formula?

 

[olushow]

i used one of the formulas posted -

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 1 - Called Weekly, I then created a group based on this formula.

I created a ilne chart as follows :

On Change of @weekly

Show Values:-
Count of TableName.UniqueID

My problem now is that when I plug in parameter of 12//06/10 through 1/30/10. The chart only gives me up til 12/19/10. It skips the rest of the weeks.

My objective is to build a line chart that gives me the total tickets opened for all companies, on a weekly basis.
The horizontal value will be the weeks, and vertical values will be the Number of Tickets opened.

 

[olushow]

Mistake on the therad title, it is Weekly Groups Sunday through Saturday.

 

[lbass]

Looks like you should have entered 1/30/11.

-LB

 

[olushow]

@LBASS - You are correct.. Thanks.


I got it working, now I need to be able to group the report weekly, but on Week ending, assuming the days run from Monday to Saturday. I''ve used some formulas that i came accross online, but the formula stills works as a Week starting, not a week ending.

Formula - {HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 2

 

[lbass]

If it is still a Sunday to Saturday week, I think you should group on this:

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 1

...and then in the group expert->options->customize groupname->use a formula->x+2, enter:

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 7

-LB

 

[IanWaterman]

Group on the formula that works.

Instead of showing the group name in header, replace with a new formula

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 2 +6

Of course you can roll the +2+6 into +8, just showing you the change.

Ian

 

[olushow]

On both examples offered, it still shows
week start date

for example

12/05/10

12/12/10


My parameters is has follows

12/06/10 - 1/30/11

The report lists the following

12/12/10

12/19/10

12/26/10

I want it to view as follow

12/12/10 - 12/19/10

12/20/10 - 12/27/10

or Weekending - 12/19/10

Or Could it be that i am not being specific enough, and that it requires more coding to get it to show in the format i want?

 

[lbass]

Then you didn't enter my suggestion correctly. Please post exactly what you did.

Are you now saying you don't want only the weekending date?

-LB

 

[olushow]

@LB

I created a formula called weekly.. Formula is as follows:

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 1

Then in the group expert-options- Customize group name option - forumla , i entered in the following formula :

{HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 7



I get the error - The formula must result in a string. AM I a missing a parenthesis somehwere?


I can live with just the weekending.

 

[olushow]

@LB.. So I made some progress, I had to use a ToText function to get it to work, but now it shows the date plus the time.

e.g. 12/11/201 :12:34:45PM, which i don't want. I just want the date.

My formula is as follows:
ToText ({HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 7)


I know I can put in a clause like "dd/mm/yyyy", but i've tried to place it somehwere after the field declaration, but crystal is not taking it..

 

[lbass]

ToText ({HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 7, "dd/mm/yyyy")

Sorry, forgot about it needing to be text.

-LB

 

[olushow]

Ok. It took it but something buggy is happening.

When i enter in the following paramenters,

12/06/10 - 01/30/11

It gives me the following group name


Total Week ending 11-29/10

Total Weeks ending 18/54//2010


My formula is as follows:
ToText ({HPD_Help_Desk.Submit_Date} - dayofweek({HPD_Help_Desk.Submit_Date}) + 7, "dd/mm/yyyy")

 

[lbass]

Please copy the formula from the customize groupname formula area.

-LB

 

[olushow]

ToText({HPD_Help_Desk.Submit_Date}-dayofweek({HPD_Help_Desk.Submit_Date}) + 7, "dd/mm/yyyy")

 

[olushow]

@lb - I got it now.

two things. I used the wrong format - dd/mm/yyyy' Since I'm based in Maryland, USA. It is always the month first "mm/dd/yyyy" - I'm assuming..

The other thing is that I remember that when using the ToText function; it should be as MM/dd/yyyy not mm/dd/yyyy ( the piece that goes at the end of the line).

That seemed to fix my problem.

So my question is what is the significance of having the month part in ALL caps? - MM/dd/yyyy instead of mm/dd/yyy

 

[lbass]

Sorry, I misled you. "M" is for months to distinguish it from "m" minutes for date formats. You can, however, format it either dd/MM/yyyy or MM/dd/yyyy as you wish.

-LB

 

[olushow]

Thanks.. One more thing.

What is the function to use, if you just wanted the chart to just show a one week period.

 

[lbass]

I'm not sure I'm following. You would just limit the records in the selection formula to a particular week. Or if you have a group on week, you could place the chart in the group section, assuming you are using some "on change of" field like company.

-LB

 

[olushow]

Just to be sure

What I am saying is that the report right is a weekly one

For example

02/05/11, 02/12/11, 02/19/11

essentially, the report would pull the same data, but the Chart will show data for a one week period.
e.g.

02/05/11; 02/06/11, 02/07/11, 02/08/11, 02/09/11, 02/10/11, 02/11/11


Like a seven day Chart line