Forum Discussion

HeatherBryant2's avatar
HeatherBryant2
Qrew Assistant Captain
9 years ago

Set Order for Summary Crosstabs & Chart Legends

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!
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    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.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      9 years ago
      The Mongolian Vowel Separator  looks promising: ᠎

      As does the Invisible Separator: ⁣

      Also, the Zero-Width Joiner works: ‍

      Lots of possibilities to try!
  • HeatherBryant2's avatar
    HeatherBryant2
    Qrew Assistant Captain
    9 years ago
    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.
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    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
    • HeatherBryant2's avatar
      HeatherBryant2
      Qrew Assistant Captain
      9 years ago
      Hi Eric, This is a formula field I'm dealing with so I don't think I can apply that method.
    • Eric_J_Hansen's avatar
      Eric_J_Hansen
      Qrew Cadet
      9 years ago
      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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    What about just regular leading spaces in the formula

    If([# of Tasks]>6,"  High",
    If([# of Tasks]>1," Medium","Low"))
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    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;
  • HeatherBryant2's avatar
    HeatherBryant2
    Qrew Assistant Captain
    9 years ago
    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. 
  • JessicaFeauto2's avatar
    JessicaFeauto2
    Qrew Cadet
    9 years ago
    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?