topic Re: Aggr function and its sorting property in QlikView

Aggr function and its sorting property

kdmarkee — Thu, 12 Oct 2017 14:59:09 GMT

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

Re: Aggr function and its sorting property

el_aprendiz111 — Thu, 12 Oct 2017 15:38:58 GMT

Hi,

Re: Aggr function and its sorting property

sunny_talwar — Thu, 12 Oct 2017 15:40:40 GMT

Check here:

The sortable Aggr function is finally here!‌

and here:

Recipe for a Pareto Analysis – Revisited

Re: Aggr function and its sorting property

kdmarkee — Thu, 12 Oct 2017 16:02:57 GMT

I reviewed those items other than pareto and it isn't quite enough for me unfortunately. Ideally I'd like to see this in action in a sample qvw to really drive it home.

Re: Aggr function and its sorting property

antoniotiman — Thu, 12 Oct 2017 16:25:50 GMT

Hi Kris,

see Attachment.

Regards,

Antonio

Re: Aggr function and its sorting property

sunny_talwar — Thu, 12 Oct 2017 16:30:26 GMT

Some more examples

sortable Aggr function issue?

Help on Cumulative Sum | Qlik Community

Re: Aggr function and its sorting property

kdmarkee — Thu, 12 Oct 2017 18:17:09 GMT

Thanks for those examples, appreciate it. I'm was trying to see if the sort feature of Aggr would help me solve my row sorting problem with the attached qvw. If you have time to look at it and offer any advice, that would be great. Maybe this qvw is as good as it will get; what I ideally want may not be possible. Thanks.

Re: Aggr function and its sorting property

swuehl — Thu, 12 Oct 2017 18:30:24 GMT

I don't think you need the Aggr() function at all here.

Create an expression that defines your sort order, e.g. something like

=Rangesum(Date,StartTime)*1E6 + Account * 1E3 + +Line

Then sort by this expression

Re: Aggr function and its sorting property

sunny_talwar — Thu, 12 Oct 2017 18:32:23 GMT

Not sure, but may be this sort order

Re: Aggr function and its sorting property

kdmarkee — Thu, 12 Oct 2017 19:04:43 GMT

That is not quite right for my desired result unfortunately. I need to have all Lines within an Account bunched together, which is why I was using the Aggr to at least accomplish that, that is one of my top requirements for the output.

Re: Aggr function and its sorting property

swuehl — Thu, 12 Oct 2017 21:27:25 GMT

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.

Re: Aggr function and its sorting property

kdmarkee — Fri, 13 Oct 2017 14:49:28 GMT

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

Re: Aggr function and its sorting property

swuehl — Fri, 13 Oct 2017 14:57:09 GMT

Have you checked my last attachment?

Date	StartTime	Account	Line	Code	sum(Units)
					99
1/1/2017	12:00:00 AM	22	2	93312	0
1/1/2017	12:00:00 AM	22	3	93503	0
1/1/2017	12:00:00 AM	22	4	36620	0
1/1/2017	12:00:00 AM	22	5	76937	0
1/1/2017	12:00:00 AM	22	6	99140	0
1/1/2017	12:00:00 AM	22	99	99100	0
1/1/2017	3:55:00 PM	22	1	33405	0
1/1/2017	12:00:00 AM	33	2	93312	0
1/1/2017	12:00:00 AM	33	3	93503	0
1/1/2017	12:00:00 AM	33	4	36620	0
1/1/2017	12:00:00 AM	33	5	76937	0
1/1/2017	12:00:00 AM	33	6	99140	0
1/1/2017	9:45:00 PM	33	1	33860	0
1/1/2017	12:00:00 AM	99	2	93312	0
1/1/2017	12:00:00 AM	99	3	93503	0
1/1/2017	12:00:00 AM	99	4	36620	0
1/1/2017	12:00:00 AM	99	5	76937	0
1/1/2017	12:00:00 AM	99	6	99140	0
1/1/2017	12:00:00 AM	99	99	99100	0
1/1/2017	7:40:00 AM	99	1	33405	0
1/1/2017	7:00:00 AM	11	1	55555	1
1/1/2017	9:00:00 AM	10	2	55555	2
1/2/2017		44	1	PC	0
1/2/2017		55	1	NC	32
1/3/2017	4:38:00 PM	20	1	23515	0
1/3/2017		30	1	NC	32
1/4/2017	12:00:00 AM	40	2	99140	0
1/4/2017	7:18:00 PM	40	1	11042	0
1/4/2017	12:00:00 AM	66	2	93312	0
1/4/2017	12:00:00 AM	66	3	36620	0
1/4/2017	7:21:00 AM	66	1	33975	0
1/4/2017	12:00:00 AM	77	2	36620	0
1/4/2017	12:00:00 AM	77	3	99100	0
1/4/2017	5:29:00 PM	77	1	27245	0
1/4/2017	1:27:00 PM	60	1	11042	0
1/4/2017	3:40:00 PM	70	1	50592	0
1/4/2017	8:30:00 PM	80	1	19367	0
1/4/2017		90	1	ND	32

Re: Aggr function and its sorting property

kdmarkee — Fri, 13 Oct 2017 15:07:54 GMT

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.

Re: Aggr function and its sorting property

swuehl — Fri, 13 Oct 2017 15:22:11 GMT

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:

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.

Re: Aggr function and its sorting property

swuehl — Fri, 13 Oct 2017 15:32:02 GMT

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.

Re: Aggr function and its sorting property

kdmarkee — Fri, 13 Oct 2017 15:48:37 GMT

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!