ademir
asked on
Help with Formula
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
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
ASKER
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???
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???
ASKER
MLMCC:
I use this on the evaluate section
dayofweek({FacMesExp.envfe
and I solve the weekend issue, how can I compare the date to an array of dates(holidays) ???
I use this on the evaluate section
dayofweek({FacMesExp.envfe
and I solve the weekend issue, how can I compare the date to an array of dates(holidays) ???
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
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
ASKER
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.envf
Well, how can I compare the date with an array of dates, so if the dates are the same, the "divisor" won't increase???
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.envf
Well, how can I compare the date with an array of dates, so if the dates are the same, the "divisor" won't increase???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. I'll be waiting for the link
Take a look at
http://support.crystaldecisions.com/communityCS/FilesAndUpdates/BusinessDays.zip.asp
I think that is it.
mlmcc
http://support.crystaldecisions.com/communityCS/FilesAndUpdates/BusinessDays.zip.asp
I think that is it.
mlmcc
ASKER
mlmcc:
I'm having trouble loadin the DLL, If I call it, I get a Division by Zero error, can you help me????
I'm having trouble loadin the DLL, If I call it, I get a Division by Zero error, can you help me????
How are you trying to use it?
mlmcc
mlmcc
ASKER
I put this on a formula field:
WhilePrintingRecords;
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
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.env
WhilePrintingRecords;
wdaysClass1wdNumWorkDays({
WhilePrintingRecords;
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
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.env
WhilePrintingRecords;
wdaysClass1wdNumWorkDays({
I'll take a look at work how I used it. I know it wasn't like that.
mlmcc
mlmcc
I created 2 formulas
Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
I put that in the report header section
Days
wdaysClass1wdNumWorkDays({
I didn't use the parameters but rather fields from the stored procedure. That one I put in the details sectioin.
mlmcc
Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
I put that in the report header section
Days
wdaysClass1wdNumWorkDays({
I didn't use the parameters but rather fields from the stored procedure. That one I put in the details sectioin.
mlmcc
ASKER
I can load the DLL, because I put this on the header,
Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
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.envfe
wdaysClass1wdNumWorkDays({
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" ???
Set_Holidays
wdaysClass1wdLoadFile ("D:\Documentos\CF\CRYSTAL
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.envfe
wdaysClass1wdNumWorkDays({
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" ???
ASKER
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}
wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}
ASKER
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}
wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}
ASKER
Thanks MLMCC, the report works the way I need,
Glad you resolved the error.
mlmcc
mlmcc
ASKER
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}
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???
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}
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???
Not sure what you are missing. What do you mean compare? Are you trying to eliminate the holidays?
mlmcc
mlmcc
ASKER
if wdaysClass1wdIsWorkDay (date({FacMesExp.envfecha}
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")
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")
Let me look at this at work. I had to do a business days calculation using this and eliminate holidays also.
mlmcc
mlmcc
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