Date and value problem

[rafeman]

Hi, hopefully I can explain this correctly.

I have a transaction table that stores budgets such as below;

01/07/10 - £1500
01/08/10 - £800

The user runs the report and has a parameter called 'SelectWeekEndDate'. Their weeks always start with the last Sunday of the week.

So if they select a week end date of 08/08/10 the budget formula will be £800 \ 31 * 7

However, if they select 01/08/10 the budget needs to be 1 day of the £1500 budget and 6 days of £800.

How do I work out in formulas how to extract the information I need and get the correcr figure?

Thanks

 

[IanWaterman]

YOur example does not make sense.

01/08/10 is a sunday and therefore the start of the next week. Why are you including budget from previous week?

YOur best option is to calculate the daily budget for each month. Then group data by week.

I do not think there is a crystal function to return number of days in a month, so us formula


@budget per day
If month(datefield) in[4, 6, 9, 11) then budgetfield/30 else
If month(datefield) = 2 then the budgetfield/{@Febdays Formula] else
budgetfield/31

You can then sum this formula in your week group footer.

Off the top of my head I can not remember a formula to determine FebDays but if you look araound I am sure there is one.

Ian

 

[fisheromacse]

i believe this will give you the number of days in the month of your date field:

day(dateserial(year({yourtable.datefield}),month({yourtable.datefield})+1,1)-1)

 

[IanWaterman]

Good catch Fisher!

I knew that really :~/

Ian

 

[rafeman]

thank you both. Will take a look ASAP

 

[fisheromacse]

Ian,

i wish i could honestly take any credit, but, as with most things i post that work, lbass deserves the acclaim, even if i may not credit her at that time.

ed