topic Complicated Top 'x' Dimension in QlikView

Complicated Top 'x' Dimension

Thu, 26 Apr 2012 21:58:04 GMT

In my pivot tables I frequently use an expression like below in one of my dimensions to return the top 'x':

=aggr(if(rank(sum(Units),4,1) < 11,Dimension1),Dimension1)

This is pretty straight forward, after I select "Do not show null values" it will only return dimensions that aggregated have the top 10 Units.

My question is how would I answer ...

"I need the top 10 based on Units BUT their Gross Margin must be over 1000"? I've been trying all sorts of things and it won't work. This will return only the one or two that qualify for top 10 Units and have a Gross Margin over 1000.

=aggr(if(rank(sum(Units),4,1) < 11 AND sum([Gross Margin]) > 1000,Dimension1),Dimension1)

I even tried embedding another if within an if and it doesn't work:

=aggr(if(rank(sum(if(sum([Gross Margin]) > 1000,Units)),4,1) < 11,Dimension1),Dimension1)

Could set analysis play a role here, possible the P() function? Any suggestions would be appreciated. Thanks!

Re: Complicated Top 'x' Dimension

msteedle — Fri, 27 Apr 2012 01:17:33 GMT

To accomplish what you are looking for, you need to evaluate the Gross Margin threshold before doing the ranking at all; otherwise you will only get Dimension1 values that meet both criteria (what your AND is doing now).

Neither approach is particularly efficient, but have you considered moving that logic to the expressions instead of the dimensions? Pivot tables are already resource-intensive enough before introducing calculated dimensions, especially involving the aggr function. Something like this:

If(Sum(TOTAL <Dimension1> Margin) > 1000,

If(Rank(Sum(TOTAL <Dimension1> Units),4,1) < 11,

Sum(Margin)

)

It could likely be accomplished using P() in set analysis, but it wouldn't be much prettier.

Complicated Top 'x' Dimension

Oleg_Troyansky — Fri, 27 Apr 2012 01:23:26 GMT

The problem here is that you validate two separate conditions - the Rank and the Margin. Only 2 out of the top 10 Dimensions can show Gross Margin over 1000. Assuming that you have more "dimension" values with high margins, your goal is to only include those values in your Ranking that satisfy the Margin requirements.

Try the following. This has not been tested, so use it "as is":

=aggr(if(rank(sum({< Dimension1 = {"=sum([Gross Margin]) >1000"} >} Units),4,1) < 11 ,Dimension1),Dimension1)

The Set Analysis condition inside the Sum(Units) should cause including only those "dimensions" with the high margin, in the ranking.

Hope it works for you.

Oleg

Complicated Top 'x' Dimension

Fri, 27 Apr 2012 16:00:43 GMT

Michael/Oleg - You guys are great, both solutions work! Michael I hadn't even thought of using the TOTAl operator in my expression. Oleg passing the results of an expression to the set analysis is brilliant using the double quotes. Thanks again, I learned a lot with this.