Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting the Horizontal dimension on a Pivot Table

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

2 Replies
Anonymous
Not applicable
Author

Not applicable
Author

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