Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

Hi

I have a mapping of data as follows:

DatePeriodFinancial YearMonthPeriod Division
01/08/20112012012011/12Aug1
02/08/20112012012011/12Aug1
03/08/20112012012011/12Aug1

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

SubprojectSubproject(T)PeriodAmountCommitted purchaseBudget
500040101Common Learning: Core2012014159.750.000.00
501239102School of Health Sciences School Funded Salaries201201688853.880.00772068.00
501239103Secondments & Temps201201265.140.002083.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.

2 Replies
MVP
MVP

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

Maybe like

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

Not applicable

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

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.

Community Browser