1 Reply Latest reply: Jan 16, 2016 9:57 AM by Gysbert Wassenaar RSS

    set analysis sum shows null values

      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.

        • Re: set analysis sum shows null values
          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.