Discussions

Expand all | Collapse all

Set Order for Summary Crosstabs & Chart Legends

Eric J. Hansen02-22-2017 23:52

  • 1.  Set Order for Summary Crosstabs & Chart Legends

    Posted 02-20-2017 20:47
    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. 
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1554927/RackMultipart20170220-87327-1jtjd89-Crosstabs_inline.jpg?1487623619">
    Thanks!


  • 2.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-20-2017 21:22
    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.


  • 3.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-20-2017 21:35
    The Mongolian Vowel Separator  looks promising: ᠎

    As does the Invisible Separator: ⁣

    Also, the Zero-Width Joiner works: ‍

    Lots of possibilities to try!


  • 4.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-20-2017 21:51
    My favorite is the Function Application character: ⁡


  • 5.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-21-2017 01:33
    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.


  • 6.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-21-2017 01:47
    > 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


  • 7.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-22-2017 19:26
    Thank you, Dan! We will look into that technique you've referenced.


  • 8.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-22-2017 23:41
    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


  • 9.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-22-2017 23:52
    Tested. Proven. See screenshots:


  • 10.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 00:00
    But where is the Mongolian Vowel Seperator?


  • 11.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 00:14
    Hi Eric, This is a formula field I'm dealing with so I don't think I can apply that method.


  • 12.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 00:22
    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.


  • 13.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 00:31
    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! 



  • 14.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 01:01
    What about just regular leading spaces in the formula

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


  • 15.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 02:48
    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;


  • 16.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 02-23-2017 17:35
    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. 


  • 17.  RE: Set Order for Summary Crosstabs & Chart Legends

    Posted 03-16-2017 18:52
    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?