Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Your MonthYear from the table "Nov-12" looked very different from your MonthYear "201211" from posting above. You need a numeric MonthYear.
- Marcus
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....
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?
I substituted the number field MonthYearNum into the expression but it still won't sort sequentially.
Thanks for the help...
Try it with avg(MonthYearNum).
- Marcus
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.
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.
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!