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



      I have a mapping of data as follows:


      DatePeriodFinancial YearMonthPeriod Division


      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.