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

# 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))
```
• ###### Re: compute only the highest year and avoid duplication of data

Hi Mark,

Are you using straight table?

May be you can try like..

Dimension:

PIN

OwnerNum

Effectivity Year

Expression:

=sum({<[Effectivity Year]={"'\$(=Max(Year))'}>}[Total Tax])

• ###### Re: compute only the highest year and avoid duplication of data

Hi Mark,

Try the attachment. It includes the following straight table:

PIN Owner All Years' Tax Most Recent Year Most Recent Year's Tax
7400 2015 3500
pin123  500020142000
pin456  240020151500