Omitting partial data for variable Dates

[krpurcel]

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.

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

Then I did the formula

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

 

[BeeBear]

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******

 

[krpurcel]

No I cannot exclude them in the selection criteria, because certain parts of the data for that day is being used.

 

[lbass]

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

-LB

 

[lbass]

Oops, I think it just should be:

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

-LB

 

[krpurcel]

lbass,


Thanks as always for taking the time to help me.

I modified the formula to

 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

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

Any other ideas?

 

[lbass]

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

 

[krpurcel]

Thanks lbass. As always, your solution works perfectly.