Finding Top N Values by Expression

[siddharthbanerjee1998]

I have a Straight Table in QlikView which has multiple dimensions and 3 expressions. The 3 Expressions are:

1. Sum(A)
2. Sum(B)
3. Sum(A)-Sum(B)

I want to show Top 50 Records for Sum(A)-Sum(B). Could anyone recommend an efficient solution for implementing the same?

Thanks in Advance!

[edwin]

have you tried dimension limits?  move the expression you want to use as the top n to the 1st expression and set the dimension limits in your cart:

[siddharthbanerjee1998]

Hi Edwin,

As far as I know, I can't move the expressions to the dimensions limit tabs. Am I missing something here? Kindly let me know.

Thanks, 

Siddharth

[edwin]

there is one catch though, QlikView uses the first expression and counts the number of results and ranks them from highest to lowest, and shows the top n results (not list of dimensions)
if an expression result is in two lines, both lines (actually combination of dimensions) will have the same rank so you may get more than 50 rows.  if this is not acceptable you need to introduce some sort of tie breaker (how do you break a tie if multiple combinations have the same rank?)  that will require some additional scripting.  but if its a simple solution, the built in limits should work

[edwin]

Dimension limits uses the 1st expression to evaluate the top n.  i meant move it to the 1st expression in the list.  in expression tab not dimension limit tab

[edwin]

your expressions should be:

1. Sum(A)-Sum(B)
2. Sum(A)
3. Sum(B)

[siddharthbanerjee1998]

Hey Edwin,

Apologies for the late reply. I was held up with a couple of things. Thank you so much for your suggestion. However, that wasn't giving me the desired output. Might be because of the complexity of the straight table? I'm not really sure.

However, just for reference, I was able to achieve the desired result by using:

If(AGGR(Rank(Sum(A)-Sum(B),4),<Column with the lowest granularity>)<51,<Column with the lowest granularity>) as a calculated dimension. I then suppressed the value when null. This successfully gave me the top 50 records. However, as I'm using AGGR in dimensions, it has significantly increased the computation time for the report. Still investigating to see if I can find a more efficient solution. 

Will keep this thread updated.

Thanks again for your help.

Regards,
Sid.

[edwin]

but that is the purpose of dimension limits.  make sure SUm(A)-Sum(B) is the first expression.

if it is not working then there must be something else going on, if you can attach a qvw that will help someone figure out what is causing the issue.

agree that aggr will slow down calculation of the chart and if you have a huge data set then it will affect the dashboard.  just compare your expression with

Sum(A)-Sum(B)