Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alec1982
Specialist II
Specialist 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
sunny_talwar

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

View solution in original post

15 Replies
sunny_talwar

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
Specialist II
Specialist II
Author

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?

sunny_talwar

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

)

sunny_talwar

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

Capture.PNG

alec1982
Specialist II
Specialist II
Author

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

sunny_talwar

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
Specialist II
Specialist II
Author

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

sunny_talwar

Sounds good

alec1982
Specialist II
Specialist II
Author

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