Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) |
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
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?
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?
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()))
)
Here is the sample attached which uses the sample data provided above
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
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
upgrading as we speak .. i will let you know shortly.. thanks so much!
Sounds good
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