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

Thu, 07 Nov 2013 11:52:31 GMT

I have a mapping of data as follows:

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.

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

swuehl — Thu, 07 Nov 2013 12:03:56 GMT

Maybe like

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

Thu, 07 Nov 2013 14:16:45 GMT

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.