Complex Calculated dimension

[#1]

I have a follow up question to a previously answered question: here

OK, so now that I have it working correctly to show me the top N companies by total sales, using a dimension like:

=if((aggr(rank(sum (SALES), [Firm Name])<=$(vNumRows)+1)

However, now I only want to show companies that have a total sales value that sits between 2 variables, vMaxSales and vMinSales.

I tried this:

=if((aggr(rank(sum (SALES)), [Firm Name])<=$(vNumRows)+1)
and (aggr(sum(SALES), [Firm Name]) >= $(vMinSales))
and (aggr(sum(SALES), [Firm Name]) <= $(vMaxSales)), [Firm Name])

which obviously doesn't work properly, because the rank function bit is still operating over the full universe of firms, and then the upper and lower bounds operate independently.

I was thinking that the logical step would be to use set analysis within the aggr(rank()) bit to specify the bounds, but it seems that set analysis doens't work in the aggr function. Is that correct?

[#2 · Accepted solution]

Hi Tim,

The solution I may suggest to you is the use of set analysis:

=if((aggr(rank(sum ({$< [Firm Name] = {'=Sum(Sales) >= $(#vMinSales) and Sum(Sales) <= $(#vMaxSales)'} >} Sales)), [Firm Name])<=vNumRows+1), [Firm Name])

Please, see attached example.

Best regards.

[#3]

the example looks good, thanks.

I do have one question though, about your set analysis.

In the set analysis bit you have [Firm Name] = {some stuff about sales}. Why is it that the stuff about minimum and maximum sales is evaluated to a firm name? I thought you have to use the P() function to get that?

[#4]

Hi Tim,

I don't understand what you mean with "P() function" but I will try to explain how the set analysis works:

The sentence "[Firm Name] = {some stuff about sales}" means the Firm Names meet the condition, because you will use this set of records for aggr funtion and IF condition based in Firm Name. You could make the sentence with another field as dimension base of your analysis.

I hope this help you to understand a little better the set analysis.

best regards.

[#5]

The P() function is something which has been added in QV9, but looking at it, I realise it's actually doing something slightly different to what you are doing anyway.

My confusion was not around how set analysis works (although I'm not an expert in it), or the structure of the rest of the dimension. I had thought that if you had something like "[Firm Name] =", then the condition to the right of the "=" sign had to specifically be firm names. If I'm reading your example correctly, you are getting that set of Firm Names based on a criteria in another field, which is something I didn't realise you could do.

So thanks for the education!

[#6]

Hi Tim,

You are right, you can compare a field based in the results of a condition even if it doesn't include in. Basicly, the sentence means: The firm name that meet the condition X, whatever the condition X.

Best regards.