2 Replies Latest reply: Nov 7, 2013 9:16 AM by Rob Franklin RSS

    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.