Group by gives good output and details gives bad output 1

[jongag1]

(posted it in Other Topics, but I guess it belongs here)

I have a report whichs generate data about past day's.
I want to sort the report in weeks and I use a function @groupbyweek:
{STARTDATE}-dayofweek({hdvdn.STARTDATE})+2
to tell Monday is the start date.

The output I get is as follows:

28 october
- 27-10-2002 120
- 28-10-2002 040
- 29-10-2002 640
- 30-10-2002 040
- 31-10-2002 030
- 01-10-2002 120
- 02-10-2002 240

4 november
- 03-11-2002 140
- 04-11-2002 030
- 05-11-2002 045
- 06-11-2002 041
- 07-11-2002 130
- 08-11-2002 060
- 09-11-2002 040

The Group By is ok, but when I look in the details it is not ok and is Sunday the starting day.
Can somebody please help me out?

Kind regards,
Age de Jong

 

[mbarron]

It looks like you've created your group to sort by the week and then moved the formula that calculates Monday to the group header.

If this is the case, all you need to do is change your group to sort on the formula that calculates Mondays instead of the date field. Mike

 

[jongag1]

The report is as follows:

First I have this record selection formula:
{hdvdn.STARTTIME} in {?Start} to {?Eind} and
{hdvdn.VDN} in ["217", "670", "901", "911", "961", "985", "986", "990"]

It gives me the needed data.

Then I insert the following group:
@groupbyweek which is the formula:
{hdvdn.STARTTIME}-dayofweek({hdvdn.STARTTIME})+2

So it starts with mondays.

The group header is hidden and so are the details, in the group footer I display the groupname (which is corrects and displays the correct mondays, sum off phone calls, and the calculated service level for all vdn's (telephone term).

The report footer than displays the totals for the whole periode.

Looking into the hidden details let me know I still have the problem as mentioned above.

For the record below is the SQL query:

SELECT
hdvdn.`VDN`, hdvdn.`STARTTIME`, hdvdn.`ACDCALLS`, hdvdn.`PERCENT_SERV_LVL_VDN`
FROM
`hdvdn` hdvdn
WHERE
hdvdn.`STARTTIME` >= {ts '2002-01-01 00:00:00.00'} AND
hdvdn.`STARTTIME` < {ts '2002-11-18 00:00:01.00'} AND
(hdvdn.`VDN` = '990' OR
hdvdn.`VDN` = '986' OR
hdvdn.`VDN` = '985' OR
hdvdn.`VDN` = '961' OR
hdvdn.`VDN` = '911' OR
hdvdn.`VDN` = '901' OR
hdvdn.`VDN` = '670' OR
hdvdn.`VDN` = '217')

I am still not able to solve my problem, tried a lot but nothing helps...

I really hope someone can help me out!

 

[mbarron]

Add the following formula it will change your @groupbyweek to text in the format of yyyymmdd:

stringvar textdate:= totext({@groupbydate});
stringvar switch;
switch:=textdate[7 to 10]+textdate[4 to 5]+textdate[1 to 2]

Then group by this formula field and suppress the field and insert your @groupbyweek field into the group header.


Mike

 

[jongag1]

mbarron,

Thank you for your reply, I have a problem with your suggested formula. My date-time fiels has the following structures:
9-6-2002 0:00:00
10-6-2002 0:00:00

is there a way to add zero's in front of single dates?

Gr,
Age

 

[mbarron]

How does your groupbyweek formula display?
Mike

 

[jongag1]

It displays: 7-1-2002

Gr,
Age

 

[mbarron]

One option would be to change the default date display in Crystal to show as mm/dd/yyyy. (File/Options/Fields tabs/date...) Mike

 

[mbarron]

Here's a fomula that will work if you don't want to change your default date formatting:

stringvar textdate:=totext({@groupbyweek}) ;
stringvar yr;
stringvar mnth;
stringvar dy;
stringvar swap;
yr:=right(textdate,4) ;

if instr(textdate,&quot;-&quot=2
then mnth:=&quot;0&quot;+mnth:=left(textdate,instr(textdate,&quot;-&quot-1)
else mnth:=left(textdate,instr(textdate,&quot;-&quot-1);

if (instrrev(textdate,&quot;-&quot-instr(textdate,&quot;-&quot-1)=1 then
dy:= &quot;0&quot;+ mid(textdate,instr(textdate,&quot;-&quot+1,instrrev(textdate,&quot;-&quot-instr(textdate,&quot;-&quot-1)
else
dy:=mid(textdate,instr(textdate,&quot;-&quot+1,instrrev(textdate,&quot;-&quot-instr(textdate,&quot;-&quot-1);


yr+mnth+dy
Mike

 

[jongag1]

Hello Mike,

Your formula works fine with my date format.
But unfortunately I still have the same problem.
It is making me crazy....

When I have no group it is okay, when inserting the formula as a group the date (in text) is okay (also the groupbyweek displays the correct monday, but the details still show a day earlier.

Kind regards,
Age

 

[mbarron]

Homer says D'oh. I thought I looked at this better than I did. I didn't look at enough of my report to see my results were wrong. Humble apologies.

Of course it's going to show us the stuff from Sunday. Think of it this way as if the dates were a number.
The original numbers (i.e. days of the week) are 1, 2, and 3. The formula converts them all to 2. (1-1+2, 2-2+2, 3-3+2) All give you 2. While all the results are 2, the original information comes from 1, 2 , and 3 which is what you're seeing.

RATS.
Mike

 

[mbarron]

Try this for your groupbyweek:
datevar adjusted;
if dayofweek({@date2})=1 then adjusted:={@date2}-6 else
{@date2}-dayofweek({@date2})+2

Mike

 

[jongag1]

Mike,

Let me start by saying I really apriciate the effort you put in my problem!

I'm just a beginner... Do you want me to replace the groupbyweek formula or do you want me to add it?
{@date2} is an unknown field in my report when replacing it, or do I need to replace it with {hdvdn.STARTTIME}, if I try this I get a 'Date is required here' after
adjusted:=

Regards,
Age

 

[mbarron]

Sorry about that. I had gotten called away and wanted to post quickly. The trouble was I hadn't copied the corrected formula.

Use the formula in place of what you have for your groupbyweek.

datevar adjusted;
if dayofweek({hdvdn.STARTTIME})=1 then adjusted:=date({hdvdn.STARTTIME})-6 else
adjusted:=date({hdvdn.STARTTIME}-dayofweek({hdvdn.STARTTIME}))+2
Mike

 

[jongag1]

Mike,

You can believe it or not, but....














IT IS WORKING!!!!

Thanks a lot,
Age