
Re: set analysis sum shows null values
Gysbert Wassenaar Jan 16, 2016 9:57 AM (in response to mark anthony pader)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.