Using Lookup to generate an expression based on Today's date

[#1]

Hi

I have a mapping of data as follows:

Date	Period	Financial Year	Month	Period Division
01/08/2011	201201	2011/12	Aug	1
02/08/2011	201201	2011/12	Aug	1
03/08/2011	201201	2011/12	Aug	1

The data set it links to does so using the Period as a common field.

Subproject	Subproject(T)	Period	Amount	Committed purchase	Budget
500040101	Common Learning: Core	201201	4159.75	0.00	0.00
501239102	School of Health Sciences School Funded Salaries	201201	688853.88	0.00	772068.00
501239103	Secondments & Temps	201201	265.14	0.00	2083.00

I want to pro-rate a budget figure based on today's date - automatically for the user without selections.

The expression will be:

=Sum(Budget)/12*[Period Division]

But how do I get it to lookup todays date, map it to a period and return the correct [Period Division] (Sep is 2, Oct is 3 etc)

Any help would be gratefully received.

I'd love to put up the .qlv but it contains too much confidential information.

[swuehl]

Maybe like

=Sum(Budget)/12* only({<Date= {$(=today())}>} [Period Division])

[#3]

Thank you, I have tried this but it is not returning a value...

I will play around with it, but if there are any other suggestions they would be gratefully received.