Top 5 values on straight table

[alec1982]

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)

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

[alec1982]

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..

[sunny_talwar]

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

[alec1982]

Send me your email address. I will ask Qlik if it is ok to share the server. I think they will be fine sine I am not using for commercial business.

The last solution didnt give any error but no values.. shows null.

[sunny_talwar]

If you can send me a private message or if you can add me as a connection, I can send you my email. I don't want to put my email here.

Best,

Sunny