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

# Calculation to count IDs from previous year in subsequent year?

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?

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