Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Table Sorting on Rangesum above() not working

Hi,

 

DateValuesYear TargetMonthly TargetDaily Target
1/1/201903364227920
1/2/201945336422792132
1/3/2019127336422792265
1/4/2019174336422792398
1/5/2019174336422792398
1/6/2019174336422792398
1/7/2019237336422792531
1/8/2019301336422792664
1/9/2019368336422792797
1/10/2019419336422792930
1/11/20194793364227921063
1/12/20194793364227921063
1/13/20194793364227921063

 

 

I have a visualization which has the following table

The date is calculated in the load script where missing dates are added using a calendar

Values is the sum(values) which I get from a table in the database table

Yearly Target is also a value from a database table

Monthly Target is calculated within the visualization using  the below formula

round(RangeSum(above(If(Day = 1 ,(BusinessDays/(max(BusinessDaysYear))*SUM(distinct Target)), 0), 0, RowNo())))

(this considers the business days and start of the month)

Daily Target is calculated using the below formula

floor(RangeSum(Above(
if(weekday(dateenrolled)='Sat' or weekday(dateenrolled)='Sun' or HolidayValue=1,0,(BusinessDays/(max(BusinessDaysYear))*SUM(distinct Target))/BusinessDays)
, 0, RowNo())))

 

The above works fine until it is sorted. I have read that Above() function is interactive and does not work if the table is sorted. 

 

I would like to know how should I go about solving this problem.

 

Thanks.

1 Reply
Highlighted

Aggr() by default sorts your data in the order the data was loaded... so for example if your Date is loaded like this

Date

1/1/2019
1/3/2019
1/2/2019

 

This will be the sorting that Aggr() will do.

But more recently, Qlik updated the Aggr() function to dynamically sorted. Check these links out

The sortable Aggr function is finally here! 

Recipe for a Pareto Analysis – Revisited 

First link will allow you to sort your Date on it's numeric value whereas the second one is more powerful (but I don't think you need it) where you can sort Date based on an expression.