set analysis sum shows null values

[#1]

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
123     mark      2016     1200          10          12010
123     mark      2016     1200          10          12010
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.

[Gysbert_Wassenaar]

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,

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)))),

//First Quarter

If($(Tmonths)=1 or $(Tmonths)=2 or $(Tmonths)=3,

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)),

)))),Pin3,owner,cyear,SEF))

Next, consider contacting an experienced Qlikview/Qlik Sense consultant to help you with this app. The expression above is kind of ridiculous. And your data model has several largish synthetic keys and some tables that aren't connected to any other tables.