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: 
kdmarkee
Specialist
Specialist

Aggr function and its sorting property

Would someone be so kind as to provide a sample (maybe a qvw) that shows how the sorting feature of the Aggr function works.  The Online Help describes it but their data and qvw sample does not include the field that they use in the written example.  And when would one use this, just if using an ABOVE or BELOW function?  Thanks.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/aggr.htm

Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})

StructuredParameter consists of a dimension and optionally, sorting criteria in the format: (Dimension(Sort-type, Ordering))

16 Replies
swuehl
MVP
MVP

Still, should be possible without advanced aggregation.

I am not quite sure why your requested result shows account 60 before 66 and 70 before 77 (if the overall sort should be based on Date + Starttime).

Maybe have a look at this attached version.

kdmarkee
Specialist
Specialist
Author

You are right, I had a few rows pasted in the wrong place in the desired result image...I missed that.  That aside however, this solution won't work because I need to also keep all lines of an account together, so for example the row with account 22 line 1 needs to be placed after the row with account 22 line 99.  Its a complex requirement, its like I have nested sorting rules to implement...and maybe it is not even possible in Qlik

swuehl
MVP
MVP

Have you checked my last attachment?

Date StartTime Account Line Code sum(Units)
99
1/1/201712:00:00 AM222933120
1/1/201712:00:00 AM223935030
1/1/201712:00:00 AM224366200
1/1/201712:00:00 AM225769370
1/1/201712:00:00 AM226991400
1/1/201712:00:00 AM2299991000
1/1/20173:55:00 PM221334050
1/1/201712:00:00 AM332933120
1/1/201712:00:00 AM333935030
1/1/201712:00:00 AM334366200
1/1/201712:00:00 AM335769370
1/1/201712:00:00 AM336991400
1/1/20179:45:00 PM331338600
1/1/201712:00:00 AM992933120
1/1/201712:00:00 AM993935030
1/1/201712:00:00 AM994366200
1/1/201712:00:00 AM995769370
1/1/201712:00:00 AM996991400
1/1/201712:00:00 AM9999991000
1/1/20177:40:00 AM991334050
1/1/20177:00:00 AM111555551
1/1/20179:00:00 AM102555552
1/2/2017 441PC0
1/2/2017 551NC32
1/3/20174:38:00 PM201235150
1/3/2017 301NC32
1/4/201712:00:00 AM402991400
1/4/20177:18:00 PM401110420
1/4/201712:00:00 AM662933120
1/4/201712:00:00 AM663366200
1/4/20177:21:00 AM661339750
1/4/201712:00:00 AM772366200
1/4/201712:00:00 AM773991000
1/4/20175:29:00 PM771272450
1/4/20171:27:00 PM601110420
1/4/20173:40:00 PM701505920
1/4/20178:30:00 PM801193670
1/4/2017 901ND32
kdmarkee
Specialist
Specialist
Author

Ok this is really weird... I did look at your sort issue_3.qvw but here is what it looks like on my end.  Is it possible you uploaded the qvw prior to saving it maybe?  I hope it is just something simple like that.    And not sure it matters, but I am on QV 12.10.  Thanks again.

sort3qvwimage.PNG

swuehl
MVP
MVP

No, I think it's saved correctly before uploading.

Maybe a version issue.

Is above what you expect?

If yes, can you double check the sort options of the chart:

2017-10-13 17_18_57-QlikView x64 - [E__Users_Stefan_Downloads_sort issue_3.qvw_].png

First priority should be Account, with a sort by expression like

=Min(RangeSum(Date,Alt(StartTime,0.99999)))

and sort numeric value also checked.

Next three dimensions are sorted by numeric value.

No sort on Code and expression.

swuehl
MVP
MVP

The 12.1 version seems to have changed indeed the sort priority when opening the app.

It seems you just need to put Account dimension on top of the sort list.

kdmarkee
Specialist
Specialist
Author

The version I downloaded had the Account listed third in the Sort Priority and I had just starting playing with that order when you responded, so that was it.  So this seems to work, even for my actual qvw which has a lot of "real" data.  Thank you so much...I didn't think I'd get to fulfill all the requirements on this one but with your help I can.  Thanks again!