4 Replies Latest reply on Sep 18, 2018 1:47 PM by Anthony Sardain

    Count distinct for only new instances of a given variable

    Anthony Sardain

      Hi all,


      I'm trying to create a map that shows the number of unique actors that are engaging in armed conflict in 5 countries. Specifically, for the purposes of my analysis, I'm trying to determine the number of "new" unique actors – i.e. actors that show up within the past week that do not have a history of acting in that region in the past year.


      For now I've managed to generate a map of the total count of actors ("Actor1") from the past week – split by region ("Admin1") – (see screenshot), however this counts even those actors that have been active prior to the week that I'm considering. How do I go about to only count the "new" actors for each region?


      Screen Shot 2018-09-17 at 6.30.04 PM.png


      I should specify that if an actor X has been active in Admin1 region "A" 6 months ago, but never in Admin1 region "B", then actor X would be considered a new actor in region B (but not in region A). Thus if X was active in the past week in both regions A and B, then actor X would contribute to the count of new actors in region B but not in region A.


      I should also note that in the given dataset, it is entirely possible that only considering new actors may result in counts of 0 across the board for all regions in all countries. 


      Thanks in advance for any indication of what I should do!