topic Re: Advanced Sorting (by field not in dimension or epxression) in QlikView

Advanced Sorting (by field not in dimension or epxression)

zagzebski — Sat, 30 Aug 2014 15:24:44 GMT

I have a field that I want to sort by but I don't want it in my chart.

My chart: Dimension is Month and expressions are previous 12 months, previous 13-24 months etc. So currently I have data through August so each expression will just have the total of September - August (the last 12 months and so on). I know I could us the match function in the expression area of sort but that would have to be changed every month then and need to be dynamic in case the selection was different. What I really want to do is sort by my MonthYear field 201309, 201310, etc. but not have it anywhere in the chart because it is doesn't allow for expressions to be on the same line since the year is included.

Any thoughts?

Re: Advanced Sorting (by field not in dimension or epxression)

marcus_sommer — Sat, 30 Aug 2014 16:17:41 GMT

You could convert your static match-expression into a dynamically expression with $-sign expansion:

match(Month, $(=Expression))

whereat expression returned your numeric/string-list of month/time-periods per loop/concat from min/max time-period or getfieldselections.

- Marcus

Re: Advanced Sorting (by field not in dimension or epxression)

MarcoWedel — Sat, 30 Aug 2014 16:52:48 GMT

In the chart's sorting tab you can define a sorting expression, i.e. You could display your month dimension but sort by your MonthYear field.

regards

Marco

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Sat, 30 Aug 2014 18:55:35 GMT

So I am not sure what would go in your expression above.

My dimension is:

Month: Jan, Feb, March, etc.

I want to sort by but not have in my dimension:

MonthYear: 201308, 201309, 201310, etc.

This way Aug would be first, then Sept, then Oct and so on

Just not sure how that works into your example from above

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Sat, 30 Aug 2014 18:56:40 GMT

But how would I sort by MonthYear in my sorting expression? Not sure of the syntax.

Thanks.

Re: Advanced Sorting (by field not in dimension or epxression)

ecolomer — Sun, 31 Aug 2014 06:15:00 GMT

Put in the expresion to order

Re: Advanced Sorting (by field not in dimension or epxression)

marcus_sommer — Sun, 31 Aug 2014 08:45:31 GMT

You aren't limited by fields in your chart - you could use all fields which are assigned together - for example if they are from the same table or connected by a key to a master-calendar or similar.

In your case will be properly an avg(MonthYear) in the sort-expression work.

- Marcus

Re: Advanced Sorting (by field not in dimension or epxression)

adamdavi3s — Sun, 31 Aug 2014 09:07:19 GMT

I tend to use =only({1}MonthYear)

This means month selections don't cause things to re-order etc

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 13:35:32 GMT

I put "MonthYear" in the sort expression but still not working. This is the data:

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 13:36:55 GMT

I tried putting that in but still not working. I included what I want to get to work in a reply above.

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 13:37:51 GMT

Thanks Enrique - I couldn't get this to work when I added it to the sort expression. Please see my reply above with the data and what I am trying to accomplish.

Re: Advanced Sorting (by field not in dimension or epxression)

marcus_sommer — Mon, 01 Sep 2014 13:45:03 GMT

Your MonthYear from the table "Nov-12" looked very different from your MonthYear "201211" from posting above. You need a numeric MonthYear.

- Marcus

Re: Advanced Sorting (by field not in dimension or epxression)

adamdavi3s — Mon, 01 Sep 2014 13:54:07 GMT

Do you have the month number in your model?

I.e. april = 01, may =02 etc? You could then use =only({1}monthno)

which I think would work....

Personally I would have a numeric month year as marcus suggests, one column of values and then control the data columns with set analysis....

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 13:57:25 GMT

Thanks Marcus.

I substituted above with the number field: MonthYearNum

It still won't sort. I just put =MonthYearNum in the expression. Is that correct?

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 13:59:42 GMT

I substituted the number field MonthYearNum into the expression but it still won't sort sequentially.

Thanks for the help...

Re: Advanced Sorting (by field not in dimension or epxression)

marcus_sommer — Mon, 01 Sep 2014 14:01:00 GMT

Try it with avg(MonthYearNum).

- Marcus

Re: Advanced Sorting (by field not in dimension or epxression)

adamdavi3s — Mon, 01 Sep 2014 14:03:04 GMT

This will not work if you have dates selected in the dashboard.

This is why you need the "only" formula as the {1} set analysis tells it to disregard any selections in the sort.

Re: Advanced Sorting (by field not in dimension or epxression)

zagzebski — Mon, 01 Sep 2014 14:18:47 GMT

Thanks for sticking with me.

Still not working. When I include the MonthYearSeq number in my dimension and sort by MonthYearSeq it works just fine (obviously) .

But when I take it out and add the expression =only({1}MonthYearSeq) in the "Month" sort it doesn't work. In fact I don't get it at all because it sorts like Dec, Nov, Jan - have no idea why.

Re: Advanced Sorting (by field not in dimension or epxression)

adamdavi3s — Mon, 01 Sep 2014 14:21:35 GMT

Are you able to upload a small example document?

I suspect we can resolve it but without seeing the model it can be quite tricky!