Set Order for Summary Crosstabs & Chart Legends

  • 1
  • 2
  • 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!
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb

Posted 2 years ago

  • 1
  • 2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
You might try using an HTML field or a text formula that contains leading spaces to force the apparent sort order you want.

​ ​ ​ High

​ ​ Medium

​ Low

where ​ is the numeric character entity for a unicode zero width space.
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
The Mongolian Vowel Separator  looks promising: ᠎

As does the Invisible Separator: ⁣

Also, the Zero-Width Joiner works: ‍

Lots of possibilities to try!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
My favorite is the Function Application character: ⁡
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
> 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:

Un-HighCharted Waters Tasks Table
https://haversineconsulting.quickbase.com/db/bk89redf6?a=td
(Edited)
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
Thank you, Dan! We will look into that technique you've referenced.
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
Tested. Proven. See screenshots:
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,694 Points 20k badge 2x thumb
But where is the Mongolian Vowel Seperator?
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
Hi Eric, This is a formula field I'm dealing with so I don't think I can apply that method.
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.
I was wrong about that. dang! 

Here is what I have resorted to in the past:

If([# of Tasks]>6,"1-High",
If([# of Tasks]>1,"2-Medium","3-Low"))

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

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
What about just regular leading spaces in the formula

If([# of Tasks]>6,"  High",
If([# of Tasks]>1," Medium","Low"))
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,994 Points 20k badge 2x thumb
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;
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
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. 
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
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?