Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.