Skip to main content
Question

Set Order for Summary Crosstabs & Chart Legends

  • February 21, 2017
  • 0 replies
  • 102 views
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!
      This topic has been closed for replies.

      • anomDiebolt
      • Quickbase Alumni
      • February 21, 2017
      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.
      • HeatherBryant2
      • Author
      • Quickbase Alumni
      • February 21, 2017
      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.
      • anomDiebolt
      • Quickbase Alumni
      • February 21, 2017
      > 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
      • HeatherBryant2
      • Author
      • Quickbase Alumni
      • February 23, 2017
      Thank you, Dan! We will look into that technique you've referenced.
      • EricJHansen
      • Quickbase Alumni
      • February 23, 2017
      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
      • EricJHansen
      • Quickbase Alumni
      • February 23, 2017
      Tested. Proven. See screenshots:
      What about just regular leading spaces in the formula

      If([# of Tasks]>6,"  High",
      If([# of Tasks]>1," Medium","Low"))
      • anomDiebolt
      • Quickbase Alumni
      • February 23, 2017
      Those ordinary spaces take up space. You need a Unicode character that does not take up any space.

      Mongolian Vowel Separator: ᠎
      Invisible Separator: ⁣
      Zero-Width Joiner: ‍
      Function Application: ⁡
      • HeatherBryant2
      • Author
      • Quickbase Alumni
      • February 23, 2017
      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", "⁡ Medium".

      It actually looks OK in the summary reports. I've set the High to have two spaces, Medium one, and Low none. 
      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", "⁡ .25"
      shows up as 0% in my table.

      Any suggestions?
      Terms & ConditionsAccessibility statement