2 Replies Latest reply: May 28, 2010 4:26 AM by DANIEL CHOTZEN RSS

    Selection on Expression based on multiple fields


      Here's a problem that I'm having. I built an expression in a List box. The expression is based on 2 fields. It basically says if EITHER field is greater than 0, make the expression a 'Y', otherwise make it 'N':

      =If(Budget>0 or Spending>0, 'Y','N')

      The problem happens when I use this list to make selections and the 'Y' value doesn't filter the result properly. It appears that when I click on the result of this expression, Qlikview is filtering the data by filtering the source fields, NOT the expression result nor the "combination" of the source fields, therefore allowing both fields to be 0 at the same time.

      Here's a qvw as sample. You can see how 0 appears in the Current Selections box under both fields, when 'Y' is selected.

      I can overcome this by using the loading script to turn the expression into an actual field. But I wonder is there a way to achieve the functionality without modifying the script.

      Any help is appreciated.

        • Selection on Expression based on multiple fields

          My experience with expressions in List Boxes is that, as you have observed, QlikView will make selections of the fields, not on an expression.

          For example, I wanted to filter a "Top selling products" chart so I only view products which sold over $1,000 last week. I created a list box with the expressions:

          Aggr(Sum(NetSales_Amt * LastWeek_BinaryFlag) , Product)

          When users select a range in this list box -- really they are searching the list box -- using ">1000", qlikView selects the associated products which have sales > 1000 for last week.

          I'm not sure how you could select anything else. QlikView makes selections of fields, so your solution of putting the expression as a field should work.

          Good luck!


            • Selection on Expression based on multiple fields
              DANIEL CHOTZEN


              actuely the qliview help talkes about this specificaly, here is an exempt from the help

              When a calculated field is used, some list box functionality will be different compared to regular field list boxes. Some property options are not available (see below). Furthermore statistics boxes cannot be created from such list boxes.

              A calculated field must always be a transformation of one or more normal fields. Purely synthetic calculated fields created via the valuelist and valueloop functions are not permitted. When making selections in a calculated field, you actually make selections in the underlying normal field(s). When using more than one field as base for a calculated field, the logical implications of selections in the base fields or the calculated field may be quite complex.

              The use of calculated fields in list boxes may cause performance issues with very large data sets.