Formula on Date

[ter79]

I have a report that returns 4 or 5 rows of information depending on the month of the year. I have a formula that highlights the week in question.

If DatePart("ww", CurrentDate)-2 = {2003_Scorecard_Extended.week#} then Silver Else White

However, when I try to use that same formula in a calculation I keep getting a zero, the formula that I'm using is

If DatePart("ww", CurrentDate) -2 = Week# Then Field1- Field2 Else 0

Field 1 and Field 2 are integers and they do contain data.

I need to find a formula that will allow me to calculate on the week that I define from the formula.

All help is appreciated

 

[Reebo99]

have you tried :

If DatePart("ww", CurrentDate) -2 = {2003_Scorecard_Extended.week#} Then Field1-Field2 Else 0

Also, DatePart("ww",CurrentDate)-2 for 1st January would be -1, is this correct?

failing this, a little bit more information, i.e. data example, expected output example, CR version and database.

Reebo
Scotland (Sunny with a Smile)

 

[ter79]

I'm using CR 8.5 with SQL 2000. The formula that you presented is what I'm using but I keep getting a zero in the field.

It's weird that the highligh formula works but it won't with the formula.

Example:

Details Section of CR

Week # | Field1
-----------------
28 | 40
-----------------
29 | 50
-----------------

Report Footer A

-----------------
| 20

Report Footer B

-----------------
| (Difference Between Report Footer A - Field1 of week # 29)

I hope this helps.

 

[synapsevampire]

Keep in mind that week numbers differ between systems, so please make sure you understand how yours is stored.

There is an ISO standard, there's the SQL Server method, and there are others...

Take the week# field and place it alongside the formula and then you can see precisely what is being returned for comparison.

You might even temporarily change the else 0 to else -99999 so that you know precisely what's happening.

-k

 

[Reebo99]

Ok, I've just tested using :

Numbervar Field1 := 50;
NumberVar Field2 := 30;
NumberVar WeekNum := 29;
If datepart("ww",currentdate)-2 = WeekNum then Field1 - Field2 else 0

Can you use this formula, but replace the items in bold with your real fields and let me know what you get back...


Reebo
Scotland (Sunny with a Smile)

 

[ter79]

Reebo,

I'm still getting a zero using the formula that you provided.

Here is what I did with the formula

Numbervar Field1 := {2003_Scorecard_Extended.Appt};
NumberVar Field2 := {@WeeklyAppt};
NumberVar WeekNum := {2003_Scorecard_Extended.week#};
If datepart("ww",currentdate)-2 = WeekNum then Field1 - Field2 else 0

 

[Reebo99]

have you tried testing each section of the formula?

In record selection, choose a record which meets the criteria your looking for. Then do formulas:

{@test1}
datepart("ww",currentdate)-2

{@test2}
{2003_Scorecard_Extended.Appt}-{@WeeklyAppt}

then, if everything looks OK, do another formula:
If {@test1} = {2003_Scorecard_Extended.week#} then {@test2} else 0

You should be able to track where the formula is failing, other than that, I'd check SV's suggestion.



Reebo
Scotland (Sunny with a Smile)