# Set Order for Summary Crosstabs & Chart Legends

• 1
• Question
• Updated 2 years ago
Hello,
I'm working with a text formula in my app that looks at a numeric field's value and formulates whether the score for a record is High, Medium or Low.

The formula is working fine, with the exception of how the formula's values (High, Medium, Low) appear in the crosstabs of summary reports and the legend in charts. By default QB alphabetizes it. This makes sense most of the time, but I'd like for the crosstabs/legend to appear in the order of High, Medium, Low.

In the past I've used a formula to add a "1", "2", "3", etc. to a Month field so they would show in chronological order, but would really rather not have any text before the High, Medium, Low values.

Thanks!
• 1,106 Points

Posted 2 years ago

• 1

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
You might try using an HTML field or a text formula that contains leading spaces to force the apparent sort order you want.

&#8203; &#8203; &#8203; High

&#8203; &#8203; Medium

&#8203; Low

where &#8203; is the numeric character entity for a unicode zero width space.
(Edited)

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
The Mongolian Vowel Separator  looks promising: &#6158;

As does the Invisible Separator: &#8291;

Also, the Zero-Width Joiner works: &#8205;

Lots of possibilities to try!

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
My favorite is the Function Application character: &#8289;
• 1,106 Points
Thank you, Dan! I tried each option. The summary report looks good, I'm not sure if you can have html in a chart legend? See attached picture to see the result in the chart.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
> The summary report looks good, I'm not sure if you can have html in a chart legend?

QuickBase may not use the feature but HighCharts supportsHTML (and other formatting properties) in legends:

http://api.highcharts.com/highcharts/legend.useHTML

So if needed you could use the Un-HighCharted Waters tecnnique to make the chart perform as you demand:

https://haversineconsulting.quickbase.com/db/bk89redf6?a=td
(Edited)
• 1,106 Points
Thank you, Dan! We will look into that technique you've referenced.
• 4,022 Points
I could be wrong, but I am pretty sure you can handle this in the field properties. Say you have a 'priority' field with the options listed as High, Medium, Low and field is set to display the options alphabetically. It will display them as High, Low, Medium. However, if you sort the options in they way they are shown in the field, and order them top to bottom = low to high, your report would probably conform. Let me know if that works. #nocoderequired
• 4,022 Points
Tested. Proven. See screenshots:

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
But where is the Mongolian Vowel Seperator?
• 1,106 Points
Hi Eric, This is a formula field I'm dealing with so I don't think I can apply that method.
• 4,022 Points
I am diving deep into my automatic memory here, but I believe that the order of true values in your formula will dictate the low to high order.

For example, if your formula looked something like this:

if([this thing]>100,"High",
if([this other thing or the same thing]<=50,"Low","Medium"))

your low to high order would be High, Low, Medium.

if it is possible to change your logic to put them in the correct order, this would probably work.

I'll test to double check.
• 4,022 Points
I was wrong about that. dang!

Here is what I have resorted to in the past:

Not sure if that is helpful. Looks like Dan the man was on the right track. Best of luck to you!

(Edited)

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
Those ordinary spaces take up space. You need a Unicode character that does not take up any space.

Mongolian Vowel Separator: &#6158;
Invisible Separator: &#8291;
Zero-Width Joiner: &#8205;
Function Application: &#8289;
• 1,106 Points
Hi guys. I've tried all the options you've provided and they still have the leading spaces. This is how the formula reads - If([Text]="Medium", "&#8289; Medium".

It actually looks OK in the summary reports. I've set the High to have two spaces, Medium one, and Low none.
• 414 Points
I have a similar question to this one. I want to change the sort order (from Ascending to Descending) in the crosstabs of a summary report.  The fields that is being used in the crosstabs is a percent field. I want the highest percent to show up first.  I've tried changing the sort order in field properties which has no impact.

I've tried creating a formula field and adding spaces before the percent (such as below) but it still sorts ascending.
If([text]=0.25, ".25",
[text]=0.5, " .50",
[text]=0.75, "  .75",
[text]=0.9, "    .90")

When I add unicode characters, the % then changes to zero.  For example:
If([Text]=".25", "&#8289; .25"
shows up as 0% in my table.

Any suggestions?