topic Top N in QlikView

Top N

Fri, 12 Nov 2010 17:31:09 GMT

Hi,

I'm trying to create a straight table chart that will display a Top N (10). My dimension is customer and the expression is sum({<SYS_ID*={'ADVANCE_1LINK'}>} TXN_COUNT). As it is now its displaying all customers where I'm trying to show just the Top 10 based in the expression. I'm not sure of the syntax although I think I may need to use the Top function. I'm not sure of the syntax - any help would be greatly appreciated.

Also, do I need to use an input box or can I just hard code the expression to be top 10?

Thanks,

Shannon

Top N

Miguel_Angel_Baeyens — Fri, 12 Nov 2010 18:03:43 GMT

Hello Shannon,

You may use the following as dimension, and leave the expression as it is:

=If(aggr(rank(aggr(sum({<SYS_ID*={'ADVANCE_1LINK'}>} TXN_COUNT), ID), 4, 2), CustomerID) <= 10, CustomerID)

You may need to change the code above, as the aggr() function used above must use the rest of dimensions in your chart. In my case there are two: ID and CustomerID.

By the way, you can use a variable instead of hardcoding the "10" so the user can select with, say, a slider object, the number of top customers he wants to display:

=If(aggr(rank(aggr(sum({<SYS_ID*={'ADVANCE_1LINK'}>} TXN_COUNT), ID), 4, 2), CustomerID) <= $(vTopNumber), CustomerID)

Hope that helps

Top N

Fri, 12 Nov 2010 18:22:12 GMT

This is what I used for a dimension and it works great - thanks!

=If(aggr(rank(aggr(sum({<SYS_ID*={'ADVANCE_1LINK'}>} TXN_COUNT), CUST_NO), 4, 2), CUST_NAME) <= 10, CUST_NAME)

One last question: Is there a way to restrict the remaining rows? My straight table chart is displaying the CUST_NAME where they are in the top 10 and displaying a dash '-' when they are not. I would like to display only 10 rows. Full disclosure - I've had no Qlikview training but am scheduled to go next month.

Thanks again for your help.

Shannon

Top N

Miguel_Angel_Baeyens — Fri, 12 Nov 2010 18:26:37 GMT

Shannon, I'm glad to help. To avoid the null value showing, you can do any of the following:

If you don't want to accumulate the values for those who don't macth that top N, just select the calculated dimension and tick the checkbox "Supress When Value Is Null".

But if you want to show then as "Others" or "Not Top 10", you can set the "else" part in the if conditional

=If(aggr(rank(aggr(sum({<SYS_ID*={'ADVANCE_1LINK'}>} TXN_COUNT), CUST_NO), 4, 2), CUST_NAME) <= 10, CUST_NAME, 'Others')

Hope that helps.

Top N

johnw — Fri, 12 Nov 2010 19:19:59 GMT

If it's OK to sort your chart by the expression descending, then you can just set a max number of 10 on the presentation tab, and not have to fiddle with calculated dimensions.

Top N

Fri, 12 Nov 2010 19:57:20 GMT

Perfect! Thanks again.

Top N

Fri, 12 Nov 2010 19:57:49 GMT

That also makes sense. Thanks for the tip.