?
Solved

Help with Formula

Posted on 2003-03-27
22
Medium Priority
?
493 Views
Last Modified: 2007-12-19
FACTURA    FECHA        HORAS     HORAS/DIAS __________________________________________________
00001      01/02/2003   798.9         798.9
00002      01/02/2003   210.7        1009.7

00004      01/03/2003   171.4         590.5

00005      01/06/2003  1055.5         745.5
00006      01/06/2003   459.4         898.6
00007      01/06/2003   131.5         942.5
............................................
............................................

This is a part of a monthly report that I need to print, but I can't make a formula in Crystal reports to do it, the formula works like this.

My report is order by date, the days, as you can see, are weekdays only, and in January 2003, WE had 22 working days, the trouble that I have, is to calculate the "Horas/Dias" column with Crystal, manually, the formula is like this:
sum(horas)/ # of workday

so we get this result for ...00004      01/03/2003

(798.9 + 210.7 + 171.4)/2 =590.5

The "2" is because 01/03/2003 was the 2nd working day,

for 00005      01/06/2003
(798.9 + 210.7 + 171.4 + 1055.5)/3 = 745.5

for 00006      01/06/2003  
(798.9 + 210.7 + 171.4 + 1055.5 + 459.4)/3 = 898.6

for 00007      01/06/2003  
(798.9 + 210.7 + 171.4 + 1055.5 + 459.4 + 131.5)/3 =   942.5

I'm using Crystal 9
0
Comment
Question by:ademir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
22 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8220321
Try this

Build 2 running totals

Display the field list
Right Click RUNNING TOTAL
Click NEW
Name - TotDIAS
Field to summarize - FACTURA
Type of summary - COUNT
Evaluate - FOR EACH RECORD
RESET - NEVER
Save

Right Click RUNNING TOTAL
Click NEW
Name - TotHORAS    
Field to summarize - HORAS    
Type of summary - SUM
Evaluate - FOR EACH RECORD
RESET - NEVER
Save

Create a new FORMULA
Right Click FORMULA
Click NEW
Name - HORASperDIAS
Enter
{#TotHORAS} / {#TotDIAS}

Save formula
Put it on the report

mlmcc
0
 

Author Comment

by:ademir
ID: 8220644
mlmcc:
The formula works, but I forgot  a detail, in my monthly report, I divide it by the number of the working day, but if a working day of my report is on saturday or sunday, the working day should not increase, In VB I handle this situation using the "weekday function" also, if a holiday is between the weekday, the working day should not increase
//////////////////////////////
January has 23 weekday, of those, I can't count January 1st as a working day, because it's a holiday, following the given example this is the situation I just mention
.

FACTURA    FECHA        HORAS     HORAS/DIAS ___________________________________________
00001      01/02/2003   798.9         798.9
00002      01/02/2003   210.7        1009.7

00004      01/03/2003   171.4         590.5

00005      01/06/2003  1055.5         745.5
00006      01/06/2003   459.4         898.6
00007      01/06/2003   131.5         942.5
............................................
............................................
                        6735.9

00021      01/10/2003   352.1        1012.6
00022      01/11/2003   972.9        1151.6
-------------------------------------------
for 00021  01/10/2003  
(6735.9 + 352.1)/7 = 1012.6
as you can see, I divide by 7, because 01/10/2003 it's the 7th working day

for 00022  01/11/2003
(6735.9 + 352.1 + 972.9)/7 = 1151.6
and here, 01/11/2003, should be the 8th working day, but because is saturday, the working day must not increase, leaving the working day on 7,

can you help me with this too???


                       


0
 

Author Comment

by:ademir
ID: 8220836
MLMCC:
I use this on the evaluate section

dayofweek({FacMesExp.envfecha})<>1 and dayofweek({FacMesExp.envfecha})<>7

and I solve the weekend issue, how can I compare the date to an array of dates(holidays) ???
0
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

 
LVL 101

Expert Comment

by:mlmcc
ID: 8221441
I see the problem.  The report only has working days reported.  Change this running total - Evaluate is what changes

Display the field list
Right Click RUNNING TOTAL
Click NEW
Name - TotDIAS
Field to summarize - FACTURA
Type of summary - COUNT
Evaluate - ON CHANGE OF FIELD
  Select the FECHA field
RESET - NEVER
Save

That should count each day only once.  The way I had it, it was counting each record therefore some days were counted more than once.

mlmcc
0
 

Author Comment

by:ademir
ID: 8225100
MLMCC:
The report is working fine, and as you can see, I'm increasing the "divisor", one for each working day,and if the working day is a weekend, then I don't increase the "divisor", as in the example, the 01/11/2003 should be the 8th working day, but, being a weekend I don't increase the "divisor" leaving it on 7, I solved this issue evaluating with a fomula "dayofweek({FacMesExp.envfecha})<>1 and dayofweek({FacMesExp.envfecha})<>7" and now if the date is a weekend, then the "divisor" is not increased.

Well, how can I compare the date with an array of dates, so if the dates are the same, the "divisor" won't increase???

0
 
LVL 101

Accepted Solution

by:
mlmcc earned 300 total points
ID: 8228882
Sorry.  Bad assumption on my part, I assumed all days were working days.  Should have listened to your warning.

Create another running total


Right Click RUNNING TOTAL
Click NEW
Name - TotDIAS
Field to summarize - FACTURA
Type of summary - COUNT
Evaluate - Formula
 (dayofweek({FacMesExp.envfecha})<>1) and (dayofweek({FacMesExp.envfecha})<>7) and ({FacMesExp.envfecha}<> previous({FacMesExp.envfecha}))

RESET - NEVER
Save

I have a user built function that determines holidays.  I'll provide a link to it when I find it.

This should work for most time periods.

mlmcc
0
 

Author Comment

by:ademir
ID: 8240162
Thanks for the help. I'll be waiting for the link
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8242107
0
 

Author Comment

by:ademir
ID: 8257404
mlmcc:
I'm having trouble loadin the DLL, If I call it, I get a Division by Zero error, can you help me????
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8257555
How are you trying to use it?

mlmcc
0
 

Author Comment

by:ademir
ID: 8257596
I put this on a formula field:
WhilePrintingRecords;
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL\BusinessDays\wdays.txt")

and that field I put it on the report header, I also changed the formula field where I was comparing the weekend

this is what I put
//dayofweek({FacMesExp.envfecha})<>1 and dayofweek ({FacMesExp.envfecha})<>7

WhilePrintingRecords;
wdaysClass1wdNumWorkDays({?dateStart},{?dateFinish}, "-23456-" )


0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8258456
I'll take a look at work how I used it.  I know it wasn't like that.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8261206
I created 2 formulas

Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL\BusinessDays\wdays.txt")

I put that in the report header section


Days
wdaysClass1wdNumWorkDays({?dateStart},{?dateFinish}, "-23456-" )

I didn't use the parameters but rather fields from the stored procedure.  That one I put in the details sectioin.

mlmcc
0
 

Author Comment

by:ademir
ID: 8261841
I can load the DLL, because I put this on the header,
Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL\BusinessDays\wdays.txt")
and when I run the program it says "OK", so, My division by Zero is when I try to compare the date with the ones on the "wdays.txt"

I compare the date on a running total field, with this formula,
dayofweek({FacMesExp.envfecha})<>1 and dayofweek ({FacMesExp.envfecha})<>7 and It works, but only for weekend days, so I change it for this formula

wdaysClass1wdNumWorkDays({?dateStart},{?dateFinish}, "-23456-" )

but I get the division by Zero error, how can I compare the date using the running total formula so I can compare them with the dates on "wdays.txt" ???
0
 

Author Comment

by:ademir
ID: 8261926
I think I got it... I'm putting this on the formula of the running total, and I'm getting no errors

wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}), "-23456-")
0
 

Author Comment

by:ademir
ID: 8261928
I think I got it... I'm putting this on the formula of the running total, and I'm getting no errors

wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}), "-23456-")
0
 

Author Comment

by:ademir
ID: 8265266
Thanks MLMCC, the report works the way I need,
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8266631
Glad you resolved the error.

mlmcc
0
 

Author Comment

by:ademir
ID: 8295047
mlmcc:
I need yout help again, as I said before, I can load the DLL because on the reports it says "OK",

when I use this clause

if wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}), "-23456-") = True then "work day" else "no work today"

so If the date is either on saturday or sunday I get the message "no work today" that's ok, but on the list that I load, I put a date that is on Wednesday, and I get the message "work day", so how can I compare the date to the ones that I have on the list???
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8295378
Not sure what you are missing.  What do you mean compare?  Are you trying to eliminate the holidays?

mlmcc
0
 

Author Comment

by:ademir
ID: 8295461
if wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}), "-23456-") = True then "work day" else "no work today"


with this formula, if the date I'm comparing is sunday=1 or saturday=7 then it returns false, this is only part of what I need, when I load the holydays list, I want to compare the date with the ones that are on the holidays list, and if they match I want to get the same false value as if it were a saturday or sunday.

On other words if the date is saturday, sunday or is on the list, I need to get the false value ("no work today")
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8295610
Let me look at this at work.  I had to do a business days calculation using this and eliminate holidays also.

mlmcc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question