1 Reply Latest reply on Mar 3, 2011 8:24 AM by Jaap Bouma

    Lookup function

    Jaap Bouma

      I want to use a lookup function to "extrapolate" measure values.

      It is possible to "extrapolate" values over the time dimension, but is it also possible to do it within another dimension?

       

      I've attached a picture that display some example data.

      The data contains numerous of people with a specific age in a specific year.

       

      The first column contains the age.

      The other columns contains the number of people with the according age.

      I only have data for the year 2010. I want to "extrapolate" the users to the other years.

       

      So in 2010 I have 10 people with the age of 4.

      This means automatically that I should have 10 people in 2011 with an age of 5, 10 people in 2012 with an age of 6 etc...

       

      Can I reach this with the lookup function in combination with another function or is this not possible? 

        • 1. Re: Lookup function
          Joe Mako

          okay, lets say you have you two data tables in text files like:

           

           

          Age,Count
          
           4,10
          5,11
          6,12
          7,13
          8,14
          9,15
          10,16
          11,17
          


           

          and

           

           

          Year
          
           2010
          2011
          2012
          2013
          2014
          


           

          Then you can make the attached workbook with custom SQL like:

           

           

          SELECT [age count#txt].[Age] AS [Age],
          
             [age count#txt].[Count] AS [Count],
            [year#txt].[Year] AS [Year]
          FROM [age count#txt],[year#txt]
          


           

          and a calc filed like:

           

           

          LOOKUP(SUM( [Count]),2010-MIN([Year]))


           

          If this does not represent your situation, please provide more details.