1 Reply Latest reply: Jul 1, 2014 6:36 AM by Gysbert Wassenaar RSS

    Creating dynamic Either/Or Criteria in a Set Analysis

    Graham Crooks

      Here’s a problem of set analysis logic that I’m trying to resolve:

       

      The calculation in Chart 1 currently takes this form:

       

      sum({<RT={F}>}
      if(((d01 like 'I21*') or (d01 like 'I22*') or ((d01 like 'R07*') and ((d02 like 'I21*') or (d02 like 'I22*'))))
      ,
      RC))

       

      i.e. d01 is one of three values, and if it’s “like R07” then d02 needs to be considered too; otherwise the value of d02 doesn’t matter.

       

      There’s another chart. Chart 2 uses a different approach, such that based on a user choice the chart will update dynamically to reflect the correct criteria for the selection made, e.g. user can click buttons 1 to 4, which updates the variable vButton; the criteria in the set analysis update according to:-

       

      sum({<RT={F},

      d01={$(=pick(vButton,vCond1,vCond2,vCond3,vCond4))},d02={$(=pick(vButton,vCond1b,vCond2b,vCond3b,vCond4b))} >}RC)

          

      The values of vCondx[b] are defined textually in the variable viewer, e.g. value of vCond1 might = ‘A21*’,’A22*’ and VCond1b could be 'B12*'; and if d02 doesn't matter then it could be simply *, which seems to work fine.

       

      This second approach works well as long as d02 has 1-1 link with d01 e.g.an AND relationship. Sadly, the scenario from Chart 1 that I want to replicate in Chart 2 has an “either/or” basis, e.g. EITHER d01 is like I21 or like I22 and the value of d02 isn’t relevant, OR d01 is like R07, in which case I need to know if d02 is like I21 or like I22 (but only if d01 is like R07).

        

      I’d like to strip out the d01/d02 dependent part from Chart 1 and represent that in the set analysis of Chart 2 using the values of vCond1 and vCond1b.

       

      So, great friends of the Qlikview Community, my question is this: Can it be done?

      Your shared insight and suggestions will be most welcome.