2 Replies Latest reply: Nov 30, 2015 10:06 AM by Andrew Walker RSS

    compute only the highest year and avoid duplication of data

      I have to compute the total tax pay per PIN. My PIN compose of (PCINUm,SINum,BInum,MDINum,Parcel,PType)

      So far I have this expression.

       

      sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PayBasic,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))
      + sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PaySEF,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))
      - sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PayDiscount,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))
      
      

      My problem is I have some duplication on my PIN and I have to identify their respective highest effectivity year. For Example;

      PIN OwnerNum Effectivity Year Total Tax

      pin123  1003    2014    2,000.00

      pin123  1002    2009    3,000.00

      pin456  4002    2015    1,500.00

      pin456  4001    2014    900.00

       

      I want to compute only for these data only;

       

      I have two columns in my table and this is my desired output in SQL query;

      PIN    OwnerNum  Effectivity Year Total Tax

      pin123  1003     2014             2,000.00

      pin456  4002     2015             1,500.00

       

      I try these expressions but it gives me zero values;

      sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr(DISTINCT max[yr] PayBasic,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) 
      + sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr(DISTINCT max[yr] PaySEF,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) 
      - sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr (DISTINCT max[yr] PayDiscount,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))