Qlik Sense Pivot Table Sorting by Field not in Pivot Table
I'm having an issue getting the sorting of a Pivot Table to work in Qlik Sense. My App has a Year filter always selected so only one year can be selected at a time. The Pivot Table in question has a topline PersonName field, then MonthName, then GoalsOrder field. The GoalsOrder represents how a PersonName made sales throughout the month and hit different Goals. Goals is a text field with values '-10' to '10' but the last row for that month will always be 'Final Goal'. If a PersonName goes up through Goals for that month the rows could look like -1, 1, 2, Final Goal. However if a PersonName goes down through Goals for that month the rows could look like 1, -1, -2, -3, -4, FinalGoal.
The issue comes in the sorting of this GoalsOrder field. I would like them to be sorted Ascending if a PersonName goes up through their Goals for that month and Descending if a PersonName goes down through their Goals for that month. I have another field called RowIndex which has the correct ordering for these rows 1 to x with the Final Goal marked as 999. I would like to sort by this RowIndex field in the GoalsOrder field in my Pivot Table but I haven't managed to get it to work.
Using the DUAL() function with these two fields causes incorrect results, I've tried sorting by the RowIndex field in Sort by Expression, I've tried some Aggregation and RANK() functions in Sort by Expression, and I've tried modifying the CSS but nothing has worked. The best I've come up with is putting RowIndex above GoalsOrder in my Pivot Table and then coloring the rows and labels white so it's "invisible" but this looks goofy. Any suggestions?
Another solution I've come up with is prepending the RowIndex field to GoalsOrder so that it is sorted automatically by RowIndex but once again this looks goofy.
