1 Reply Latest reply on Nov 1, 2017 8:29 AM by swaroop.gantela

    Calculation to count IDs from previous year in subsequent year?

    Alex Dixon

      Hello! I'm having trouble writing a calculation that counts the number of distinct IDs that were in the previous YEAR that appear in the following YEAR. I need to do this to identify returning individuals to a particular location (think of a national park, for example).

       

      So, using the table below, in 2016, the distinct count of IDs that appear that also appeared in 2015 would be 2. For 2017, it would be 3.

       

      YEARID
      20153000290

      2015

      7100005
      20152900010

      2016

      9000002
      20167100005
      20162900010
      20179000002
      20177100005
      20172900010
      20174500026

       

      Any help is greatly appreciated!

        • 1. Re: Calculation to count IDs from previous year in subsequent year?
          swaroop.gantela

          Alex,

           

          This may not completely work for your final setup,

          but as first step, you could try a combination of Lookup and Table Calculation:

           

          [ID in prev year]:

          IF LOOKUP ( ATTR ( [Year] ) ,-1 ) = ( ATTR ( [Year] )-1 ) THEN 1 ELSE 0 END

           

          [Year Total]:

          IF FIRST()=0

          THEN WINDOW_SUM ( [ID in prev year] )

          END

           

          Please note: this requires a particular set up for "Compute Using"

          when edit the table calculation.

           

          [ID in prev year] is set to "Specific Dimensions" of [ID] then [Year]

          At the level "Deepest", Restarting every [ID].

           

          [Year Total] is nested with [Year Total]'s Compute Using set to [ID] and

          [ID in prev year] as described above.

          1 of 1 people found this helpful