0 Replies Latest reply: Mar 13, 2016 9:01 PM by mark wall RSS

    How to compute only max year per pin or owner

    mark wall

      I have this data:

       

       

      PIN     OWNER     YR     YEAR1     TOTALPAY

      111      mark          2008     2009       1,000

      111      mark          2009     2009       1,000

      111      mark          1999     2009       1,000

      222     baisah         2015     2016         999

      222     baisah         2008     2016         999

      222     baisah         2017     2016         999   

       

       

      I want to sum only data that are underlined and bold. This is my expression but it turns out getting all summation of data. The data should have yr <= year1 per PIN.

       

       

      Sum({<TotalPay={">=0"}, TotalPen={"<=0"}, PayPenalty={"<=0"}, qtr1={"1","2","3","4"} >}

      aggr(DISTINCT if (max(yr)<=year1, TotalPay), TotalPen, PayPenalty,BrgyName,qtr1,year1,Pin3,yr))