topic Re: Custom Sort Order Not Working - Bar Chart in App Development

Custom Sort Order Not Working - Bar Chart

shansen — Wed, 14 Aug 2024 15:44:57 GMT

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?

Re: Custom Sort Order Not Working - Bar Chart

chriscammers — Wed, 14 Aug 2024 17:08:54 GMT

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...;

Re: Custom Sort Order Not Working - Bar Chart

shansen — Wed, 14 Aug 2024 23:38:22 GMT

Chris,

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.

Anyway, thanks for your help.

Re: Custom Sort Order Not Working - Bar Chart

Kushal_Chawda — Thu, 15 Aug 2024 09:31:08 GMT

@shansen try this expression

Match(only({1}[combined_report.STAGES]),'SQL','SQO','Closed Won')

Re: Custom Sort Order Not Working - Bar Chart

shansen — Mon, 19 Aug 2024 15:29:06 GMT

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!