Horizontal dimension not sorting properly in pivot table

[#1]

I have pivot table for a sales forecast. The last dimension is the month in which the sale is forecast to close. I 'pull' this dimension up to the horizontal position, so that we get something like (many unnecessary dimensions omitted from the table) (note: the Horizontal dimension, CloseByMonth is sorted in order Jan, Feb, Mar)

Sales rep	Company	Product	Jan 2015	Feb 2015	March 2015
John	ABC	X2000	$0	$2500	$0
John	DEF	X2000	$3000	$0	$0
Tom	HIJ	X3500	$1000	0	$2500
Tom	JKL	X2000	$0	$0	$3400
Harry	MNO	X3500	$2200	$0	$0

Up until yesterday, the reports looked fine. Now, the "CloseByMonth" is sorted randomly - e.g. 2015-JAN, 2014-MAr, 2015-DEC, etc.

I have a field called MN, for 'monthnumber', calculated from (Year(CloseByMonth)-2010)*12+Month(CloseByMonth). This gives me an integer that rises with each month (e.g. Dec 14 is 60, Jan 15 is 61, Feb 15 is 62, etc.). I have tried sorting the first dimension in the table by this field, and the last dimension in the table by this field (while removing the sorts on all intervening dimensions). Nothing is working.

Is there a property I need to check/uncheck? Is there something else at work here? I have built at least a half a dozen tables like this, and they were all fine until a couple of days ago.

thanks,

Kevin

[rubenmarin]

Hi Kevin, can you try to sort last dimension by?:

Min({1} monthnumber)

[#3]

Hi Ruben,

Nope, that didn't work. Frankly, I don't understand - how would sorting by the minimum value of OppMN for the entire document even be achievable? Since OppMN is an integer, this is like "sort by 37".

I appreciate the suggestion, though.

[rubenmarin]

Hi, I meant to say Min({1} MN)

Sometimes, if there is no data for a month, when you sort by a related field like in this case (sorting CloseByMonth using MN), Qv can't reach that related field because of the selections, the {1} will tell QV to ignore selections, and sorting with this expression is telling:

No matter wich selections there are, sort my CloseByMonth field using the minimum MN for that month. (it should be only 1 MN for each CloseByMonth, so you can use Min(), Max(), Only()..., but you need one of this functions to use the {1})

I used this many times, obviosly it's possible this doesn't works in your document because there can be a lot of things and is difficult to give the correct answer without a sample to debug.

Hope this helps.

[#5]

Thanks, I see what you are driving at, but that's not the issue here. In my script:

LOAD

     (Year(ClosebyMonth)-2010)*12+Month(ClosebyMonth) As OppMn,

     *;

SQL Select

    (buncha fields)

     ClosebyMonth,

     (buncha fields)

FROM OpMgr OM

So, both ClosebyMonth and OppMN are in the same table, and there is a value for OppMN for every CloseByMonth.

I never had to sort it explicitly before - I just pulled the dimension up into the horizontal position, and everything was fine. Don't know what changed!

I'd upload the .qvw, but it's huge.

[#6 · Accepted solution]

I am terminally stupid!!

My OppMN was calculated from the STARTDATE, not the CloseBy date. Once I made that change, sorting by OppMN worked perfectly.

I apologize for wasting people's time.