Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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])
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 | 5000 | 2014 | 2000 |
pin456 | 2400 | 2015 | 1500 |