Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Sales forecast pivot tables, where there are bunch of dimensions (sales rep, product, company, etc.), and one expression: Sum(forecast amount). The very last dimension is "expected close month", which is a text string created from:
"Year(CloseDate)&'-'&Month(CloseDate). I 'pull up' the last dimension to create my pivot table.
Understandably, my users want to see: 2015-JAN, 2015-FEB, 2015-MAR, etc. as the order in the horizontal dimension. Anyone can tell that this dimension can't be sorted alphabetically to be in correct chronological order (it goes Apr, Aug, Mar, May, etc), and it can't be sorted numerically.
I created in my LOAD statement a "month number" "CMN") from (Year(CloseDate)-2010)*12+Month(CloseDate). This gives me a monotonically increasing integer. However, if I go into Properties -> Sort, and select my last dimension and try to sort it by expression, using CMN, I still get wildly inconsistent results (as in, I can't figure out how it's being sorted!).
Questions:
If I want to sort on the last dimension in the pivot table, can I sort on any previous dimension as well, or only the last one?
Is there another way to get a 'waterfall effect' in a pivot table (not a bar chart!) where, for example, you see January forecast in first column and first (say) 8 rows, February forecast in second column and (say) rows 9-12, March in third column for rows 13-18, etc. ?
thanks,
Kevin
I've found something of a workaround, so I'll post it here in case it helps anyone else, but I'm sure there's a better solution somewhere:
I added the 'month number' CMN as my FIRST dimension (and made the column skinny and the text white so it doesn't show) and sort that by CMN. Then I clear all other sorts EXCEPT my last dimension (the expected close month), which I also sort by CMN.