set analysis sum shows null values

Fri, 15 Jan 2016 06:24:38 GMT

i have to create reports showing the total tax due, amount due and penalty of each owner in my delinquent reports. My expression/script works fine and able to compute the right computation per owner, only if I use the search functions in straight table to check for Specific PIN. However, my problem is when an owner doesn't have previous tax year, the computation or total in straight table display as - (NULL). But when an owner have previous taxes it shows the totality of taxes, penalty and amount due. For example I have this data;

PIN OWNER YEAR TAXDUE PENALTY AMOUNTDUE

123 mark 2016 1200 10 12010

This data will not show the totality in straight table per year, but the expression able to compute If I search for specific PIN or owner. However if PIN 123 have tax due below 2016, computation will display in straight table.

I attached my qvf file so anyone can check what's the problem. The name of application is practice2. Right now I can't verify if my problem have something to do in data model or in my expression.Thank you so much for your help.

Re: set analysis sum shows null values

Gysbert_Wassenaar — Sat, 16 Jan 2016 14:45:03 GMT

Try this expression:

//Second Quarter

sum(aggr(

If($(Tmonths)=4 or $(Tmonths)=5 or $(Tmonths)=6,

if((count({<cyear= {$(Tyear)}>} aggr(DISTINCT Basic,SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))=4),

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT IdleTax,qtr,OwnerNum,cyear,BINum,PCINum,SINum,MDINum,Parcel,PType))),

//Third Quarter

If($(Tmonths)=7 or $(Tmonths)=8 or $(Tmonths)=9,

if((count({<cyear= {$(Tyear)}>} aggr(DISTINCT Basic,SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))=4),

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<=$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT IdleTax,qtr,OwnerNum,cyear,BINum,PCINum,SINum,MDINum,Parcel,PType)),

if((count({<cyear= {$(Tyear)}>} aggr(DISTINCT Basic,SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))<=3),

sum({<cyear= {"$(Tyear)"}, qtr={"2","3","4"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"$(Tyear)"}, qtr={"2","3","4"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"$(Tyear)"}, qtr={"2","3","4"}>} aggr(DISTINCT IdleTax,qtr,OwnerNum,cyear,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT Basic,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT SEF,qtr,cyear,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) +

sum({<cyear= {"<$(Tyear)"}, qtr={"1","2","3","4"}>} aggr(DISTINCT IdleTax,qtr,OwnerNum,cyear,BINum,PCINum,SINum,MDINum,Parcel,PType)))),

//Fourth Quarter

If($(Tmonths)=10 or $(Tmonths)=11 or $(Tmonths)=12,