Issue in applying exclusion logic in Qlikview

[#1]

Hi,

I am facing an issue in Qlikview while replicating BO report in Qlikview.

I have the following logic in BO report.

  S_NO  NOT IN  ('S005807', 'S006592') and

  ( COUNTRY  IN  ('AON LIMITED UK')

  AND  (I_NO  NOT IN  ('0000005', '0000056', '0000570')

  OR   AONREDATA.ASSOC.I_NO  IS NULL  )

  OR   (AONREDATA.ASSOC.I_NO  NOT IN  ('0014838')

  OR   AONREDATA.ASSOC.I_NO  IS NULL  )

  AND  COUNTRY  NOT IN  ('AON LIMITED UK'))

We have provided Input boxes to users to enter COUNTRY, I_NO and S_NO values on the dashboard. We have stored them in variables and tried to replicate the above logic in Qlikview as below.

if(((Index($(vSNo), [S_NO])=0)),

if( ((Index($(vCountry1), [Country]) >0)

and (Index($(vINo1), [I_NO]) = 0 or isnull([I_NO])=-1)

or ((Index($(vINo2), [I_NO]) = 0 or isnull([I_NO])=-1)

and (Index($(vCountry2), [Country]) =0 )))

,sum([Amount]),0),0)

I have S_NO and Period as Dimensions and the above expression in Expression. Some of the values are not excluded properly and thus resulted in wrong amounts. If I add I_NO, Country fields as dimensions then I received correct values but S_NO will duplicate. User does not want duplication of S_NO field.

Please advise on this.

Thanks & Regards,

Devaki

[#2 · Accepted solution]

Replicate the logic in a variable and use the Amount field if the condition is true and ‘0’ if it is false. 

Then in the charts Expression editor take the sum of variable (sum ($(var))). This solved my issue.