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

[#1]

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:

LN

SG

NY

TK

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

[Gysbert_Wassenaar · Accepted solution]

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)