Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Omitting partial data for variable Dates

Status
Not open for further replies.

krpurcel

IS-IT--Management
Dec 1, 2003
77
US
I have a report that pulls KPIs by Day for a date range entered by the user. I need to be able to exclude item on certain days during the year. The number of days and the dates to be excluded will change.
I thought I would create an ExcludedDays list similar to Ken Hamady’s HolidayList, then do a simple if/then formula on each row and use that for a key in my calculations.
I created this ExcludedDays formula.
Code:
BeforeReadingRecords;
DateVar Array ExcludedDays := 
[
Date(2011,01,01),
Date(2011,01,04)
];
0
Then I did the formula
Code:
if {DVDN.row_date} in {@ExcludedDays} then 1 else 0
That fails, saying a date range is required at {@ExcludedDays}.

Not sure if my problem is syntax, or if this is bad way to solve my problem. Please let me know what you think.

Thanks
 

Is is possible to just exclude the days in the selection criteria of the report?

EG

and
Not({DVDN.row_date}) in [Date(2011,01,01),Date(2011,01,04)])



regards

BB

*** Count your blessings not your problems******
:)
 
No I cannot exclude them in the selection criteria, because certain parts of the data for that day is being used.
 
beforereadingrecords;
DateVar Array ExcludedDays;
if {DVDN.row_date} in ExcludedDays then 1 else 0

-LB
 
Oops, I think it just should be:

DateVar Array ExcludedDays;
if {DVDN.row_date} in ExcludedDays then 1 else 0

-LB
 
lbass,


Thanks as always for taking the time to help me.

I modified the formula to
Code:
 DateVar Array ExcludedDays;
if {DVDN.row_date} in {@ExcludedDays} then 1 else 0
but I am still getting an error message saying "a date range is required here" and highlighting {@ExcludedDays} in that formula.

My ExcludedDays formula is
Code:
 BeforeReadingRecords;
DateVar Array ExcludedDays := 
[
Date(2011,01,01),
Date(2011,01,04)
];
0

Any other ideas?
 
No, you must reference the date variable directly. I assumed that you left your original excluded days formula in the report header. You then create the second formula:

DateVar Array ExcludedDays;
if {DVDN.row_date} in ExcludedDays then 1 else 0

...and place it in the detail section. Excluded Days already has its values established in the report header. You can't reference the formula itself, since it returns a 0, not the array.

-LB
 
Thanks lbass. As always, your solution works perfectly.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top