Calculate a sum based on the 1st 15 business days of a month 1

[ecandie]

I need to create a formula that will summarize the sales information for only the 1st 15 business days of the month.

Thank you in advance...

 

[lbass]

I think you could use a formula like the following in the detail section:

whileprintingrecords;
datevar array holidays := [date(2005,01,01),date(2005,07,04)];
//add your business holidays into the array
numbervar cnt;
numbervar sumsales;

if not({table.date} in holidays) and
not(day({table.date}) in [1,7] //add your non-business weekdays
then cnt := cnt + 1;
if cnt in 1 to 15 then
sumsales := sumsales + {table.sales};

Then use a display formula like the following in the report footer:

whileprintingrecords;
numbervar sumsales;

This assumes that your report has a selection formula limiting it to one month.

-LB

 

[ecandie]

Thanks

 

[ecandie]

This is working well if I build the values for holidays. Is there a way to just pull the first 15 days of data from the database? Can we count the distinct number of dates and only pull the first 15 "data days" instead of the actual first 15 days of the month?

Thanks in advance...

 

[lbass]

What version of CR are you using, and what kind of database are you using?

-LB

 

[ecandie]

I'm using version 9 and an Informix database.

Thanks.

 

[lbass]

Well, I don't really know the syntax you would use for Informix, but you could try something like:

SELECT FIRST 15 sales, date
FROM table
WHERE date >= '01-OCT-05'and
date < '01-NOV-05'
ORDER BY date ASC

Or it might be "top" instead of "first". Also not sure how literal dates are entered for Informix. You might try going to one of your other reports and check database->"Show SQL query" area to get an idea of syntax. To use the above query, you would go to database->database expert->add command and enter the SQL query there.

Perhaps someone who knows Informix could fix the above.

-LB