15 Replies Latest reply: Jul 2, 2016 7:17 AM by Sunny Talwar

# 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)
• ###### 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?

• ###### 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?

• ###### 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()))

)

• ###### Re: Top 5 values on straight table

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

• ###### 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

• ###### 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

• ###### Re: Top 5 values on straight table

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

• ###### Re: Top 5 values on straight table

Sounds good

• ###### 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

• ###### Re: Top 5 values on straight table

I can only wish I can give you an answer of why this might not be working. My knowledge of Qlik Sense settings are very limited. May be reddys310 can offer some advice here

• ###### 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

• ###### Re: Top 5 values on straight table

thanks Sunny for all the help.. the issue is now my expression looks like

Rank(

RangeSum(Above(

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

-

0.06)

*

([Date] - above([Date])),

0,RowNo())))

and not sure how I can switch it to the new logic

BTW, I have  a QlikSense testing server on amazon. I am happy to share it with you..

• ###### Re: Top 5 values on straight table

Hands on experience with QlikSense server... oooo I would love that

Can you try this as the dimension

If(Aggr(

Rank(

RangeSum(Above(

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

-

0.06)

*

([Date] - above([Date])),

0,RowNo())))

, (Date, (NUMERIC))) <= 5, Date)

Expression:

Aggr(

Rank(

RangeSum(Above(

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

-

0.06)

*

([Date] - above([Date])),

0,RowNo())))

, (Date, (NUMERIC)))