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: 
zagzebski
Creator
Creator

Advanced Sorting (by field not in dimension or epxression)

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?

18 Replies
zagzebski
Creator
Creator
Author

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.

marcus_sommer

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

- Marcus

adamdavi3s
Master
Master

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

zagzebski
Creator
Creator
Author

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?

zagzebski
Creator
Creator
Author

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

Thanks for the help...

marcus_sommer

Try it with avg(MonthYearNum).

- Marcus

adamdavi3s
Master
Master

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.

zagzebski
Creator
Creator
Author

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.

adamdavi3s
Master
Master

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!