I have categories in my bar chart based on the [combined_report.STAGES] field. I want the stage output to always appear in a specified order, so I go to Sorting > Custom > Sort by expression > Ascending > Expression = Match([combined_report.STAGES],'SQL','SQO','Closed Won'). However, this is not making the order appear as SQL, SQO, Closed Won. (See Stages out of order.png attachment.) I even made the categories be based on the same formula = Match([combined_report.STAGES],'SQL','SQO','Closed Won'), and then just did straight-up numeric sorting. Even then the numbers appear out of order. (See Numbers out of order.png.) I am not sorting by the Actual or Forecast values at all. (See Sort order selections.png.)
Is the custom sort order functionality broken, or am I doing something wrong?
This is very strange, I was able to do it four different ways.
The one I would recommend is using the Dual() function to force a numeric sort order on the field in the data model load script.
The other approaches are variations based on what you did and then the load order of the field. I think the screen shots have enough info to help figure it out.
//use this in the load of the STAGES Field
//the match function returns zero when no match is found so
//that would mess things up.
Load
Dual(STAGES,Match(STAGES,'SQL','SQO','Closed Won')) as STAGES
From...;
I really appreciate the response. I tried what you suggested (Dual(STAGES,Match(STAGES,'SQL','SQO','Closed Won')) as STAGES in the load statement), and it didn't change the behavior. It still jumps around when I make different selections. I think I am just going to have to sort on something else.
I have a theory that the set statements on the Actual and Forecast values in the bar chart, which remove one of the STAGES values, must be confusing the sort order somehow. Perhaps it is related to values being marked as zero in the Match statement even though they don't appear on the chart. Maybe if it comes up again sometime I will take the time to sanitize the data and give an example App that reproduces the problem. For now, I think I need to move on.
Kushal, Thanks for the response. I cannot use the {1}, as my App needs to respond to selections on the sheet. I did try the only() function, though, The chart still responded the same - The SQL, SQO and Closed Won columns flop around depending on other selections made. It's a mystery to me. Good try, though!
