1 Reply Latest reply: Jun 16, 2014 3:20 PM by Gysbert Wassenaar RSS

    Ensuring only specific field values are used in a column and using RANK()

      Hello all,


      I am new to Qlikview and am loving learning the coding side of things at the moment. However i make progress and then get stuck for hours on end. Any help on the following would be great!


      I have a field called [Strategy Group] (contains Vanilla, Exotic, and Vanilla + Exotic) and I have a field called [Billing Region] which contains LN, SG, NY and TK.


      I have created a table which just have 'Overall Vanillas' and am using this formula to obtain that field value:


         =aggr(only({<[Strategy Group] = {'Vanilla'}>} [Strategy Group]),[Strategy Group])


      However I now want another table which shows 'Vanillas' but also essentially filters out the regions:







      I imagine it will be some form of the above but as I am pulling filed values from two different fields [Strategy Group] and [Billing Region] Im not sure how to do it.


      In addition, each field value will have a number assigned to it, and I want to rank each number so that it shows the rank number in the column, i.e. 1st, 2nd out of the data given.

      Thank you in advance

        • Re: Ensuring only specific field values are used in a column and using RANK()
          Gysbert Wassenaar

          You're now using a calculated dimension: =aggr(only({<[Strategy Group] = {'Vanilla'}>} [Strategy Group]),[Strategy Group]).


          If you have a limited number of expressions then it's usually better (performance wise) to add the set modifier to the expressions, instead of using a calculated dimension.


          If you want to limit values from two dimensions you can put both in the set modifier: =sum( {<[Strategy Group] = {'Vanilla'}, [Billing Region]={'LN','SG','NY','TK'} >} MyValue)