Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alec1982
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?

 

DateSale
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

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

Capture.PNG

15 Replies

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?

alec1982
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?

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

Capture.PNG

alec1982
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

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

alec1982
Valued Contributor II

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

alec1982
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

Community Browser