10 Replies Latest reply: Apr 16, 2015 3:25 AM by Amit Saini

# Limit Top 10 by Expression

Hey guys,

My problem is that I have a table with 4 measures and I want to limit the table to the top 10 based on the expression in the 3rd column (Increase #) while leaving that column in that place for presentation purposes.

Right now it looks like this:

When I limit by top 10 it limits by measure 'Before 2014/15' when I want it to limit by 'Increase #'.

I get this to work by moving the 'Increase #' measure over to the furthest left, but I want it to stay in its column position in the above 2 pictures for presentation purposes.

I also wanted to limit to top 10 a bar chart with the same expression that 'Increase #' has without actually having that measure in the bar chart.

Is there any way this can be achieved?

• ###### Re: Limit Top 10 by Expression

Hi James.

I think yes. Probably with Rank function.

Could you provide the sample of your app?

Regards,

Sergey

• ###### Re: Limit Top 10 by Expression

Hi Sergey,

Unfortunately I can't provide the app, but the 'Increase #' expression is:

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2014/15'}>}Title) -

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

And the before 2014/15 expression is:

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

Is there a way I can incorporate the Rank function into these?

• ###### Re: Limit Top 10 by Expression

Try something like this:

For 'Increase #'

IF(Rank(

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2014/15'}>}Title) -

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

)<11,

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2014/15'}>}Title) -

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

)

For before 2014/15 expression

IF(Rank(

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2014/15'}>}Title) -

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

)<11,

Count({\$<Title_Status = {'Active'}, Fee_Fiscal_Year = {'2013/14'}>}Title)

)

• ###### Re: Limit Top 10 by Expression

Hi Sergey,

Unfortunately that's not working for me, and I'm not sure how to incorporate that into my bar chart.

This would be so much easier if I could limit top 10 by an expression which isn't in the chart.

• ###### Re: Limit Top 10 by Expression

Hi,

1. add another column and move it to the top as column1.

2. Put Column(4) as Expression. The one which was column 3 before.

3. Use Dimension Limits to narow down to top 10

Hope this helps

Tobias

• ###### Re: Limit Top 10 by Expression

Hi Tobias,

Thanks for the suggestion. I would do that, but I would then have 2 identical columns in my table.

I'm also getting an internal engine error whenever I do that too.

Plus I'm not able to copy over this measure to my bar graph below, which I only want to limit by the measure while not incoprporating it into the bar chart.

Is this a feature which Qlik Sense currently does not have?

• ###### Re: Limit Top 10 by Expression

Hi James,

Check if this helps you.

Thanks,
AS

• ###### Re: Limit Top 10 by Expression

Hey Amit,

Thanks for that, but when I downloaded them no sheets were available on the app. I'm using Qlik Sense if that makes a difference.

• ###### Re: Limit Top 10 by Expression

Sorry James no idea on this , but concept wise please see below:

Ranking Concept:

Syntax:

=rank(sum(Investment),1,1)

AND

=aggr(if(Rank(sum(Investment))<=3,Investment),Investment) (For calculating Top 3)

AND

=if(rank(sum(Investment),1,1)=1,'# 1')

I have a data table which includes the following fields; SystemID, Timestamp.

I want to derive a new field in this table which ranks the Timestamp in ascending grouped by SystemID

I'd like to do this in the script.

1. e.g.

SystemID, Timestamp,   [Rank]

00001, 01/01/2013 10:00,   1

00001, 01/01/2013 12:00,   2

00001, 01/01/2013 12:30,   3

00002, 01/01/2013 11:00,   1

00002, 01/01/2013 12:12,   2

00003, 01/02/2013 10:30,   1

Syntax:

SystemID, Timestamp,

if(SystemID=previous(SystemID),peek('Rank')+1,1) as Rank

FROM ...mysource...

ORDER BY SystemID, Timestamp;

Thanks,

AS

• ###### Re: Limit Top 10 by Expression

James,