# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Valued Contributor II

## Top 5 values on straight table

hi all,

I have a straight table in Sense with Date and Sales as shown below. the same column is calculated from other columns and involve the use of above column.

Here is the expression I am using:

((Sum({\$Flag = {'1'}>}[Price]) - above(Sum({\$<Flag = {'1'}>}[Price])))  /  ([Date] - above([Date]))

I need to add more calculation and have the measure to show rolling values meaning Aug 16 should show 8.07 +  the above 4.85= 12.92 and Oct 16 = 2.35 +  the above 12.92 = 15.27 and so on.. then I need to do limit data to show top 5 only..

Anyone knows how to do it?

 Date Sale Jul 16 - Aug 16 \$4.85 Sep 16 \$8.07 Oct 16 \$2.35 Nov 16 \$1.85 Dec 16 \$1.07 Jan 17 \$0.35 Feb 17 \$0.35 Mar 17 \$0.35 Apr 17 (\$0.43) May 17 \$0.35 Jun 17 (\$0.43) Jul 17 \$0.35 Aug 17 \$0.35 Sep 17 (\$0.93)
1 Solution

Accepted Solutions
MVP

## Re: Top 5 values on straight table

May be try this:

Calculated dimension:

If(Aggr(Rank(RangeSum(Above(Sum(Sale), 0, RowNo()))), (Date, (NUMERIC))) <= 5, Date)

Expression:

Aggr(RangeSum(Above(Sum(Sale), 0, RowNo())), (Date, (Numeric)))

And then uncheck  'Show Null Value' for the dimension

15 Replies
MVP

## Re: Top 5 values on straight table

May be this:

RangeSum(Above(

((Sum({\$Flag = {'1'}>}[Price]) - above(Sum({\$<Flag = {'1'}>}[Price])))  /  ([Date] - above([Date])),

0, RowNo()))

For showing top 5, you most recent 5 numbers or top 5 cumulative numbers?

Valued Contributor II

## Re: Top 5 values on straight table

hi Sunny,

This expression gives me the numbers correctly but I need to show only the top 5 values(cumulative). when trying to do limit to top 5 five in the dimension in QllikSense I get weird values.. Is there anyway I can look for the highest 5 results and only display them?

MVP

## Re: Top 5 values on straight table

Try this:

If(Rank(

RangeSum(Above(

((Sum({\$Flag = {'1'}>}[Price]) - above(Sum({\$<Flag = {'1'}>}[Price])))  /  ([Date] - above([Date])),

0, RowNo()))

) <= 5,

RangeSum(Above(

((Sum({\$Flag = {'1'}>}[Price]) - above(Sum({\$<Flag = {'1'}>}[Price])))  /  ([Date] - above([Date])),

0, RowNo()))

)

MVP

## Re: Top 5 values on straight table

Here is the sample attached which uses the sample data provided above

Valued Contributor II

## Re: Top 5 values on straight table

this is awesome. the only thing now is how do I get rid of the nulls. I opened your sample and I see them there while I dont see them on the screenshot

MVP

## Re: Top 5 values on straight table

Are you using Qlik Sense 3.0? If not, then I think there was a bug which causes this issue. I know Stefan came up with a way to hide them, but I never paid attention to his response in another thread. I can't even say if his alternative will work here or not, but if possible, upgrade to Qlik Sense 3.0 and you should be good

Valued Contributor II

## Re: Top 5 values on straight table

upgrading as we speak .. i will let you know shortly.. thanks so much!

MVP

## Re: Top 5 values on straight table

Sounds good

Valued Contributor II

## Re: Top 5 values on straight table

so I have tested it on desktop version and it works. but on the 3.0 server it doesnt even I am on V3.0