Formatting a Percent Formula in an Answer

Have the following formula:

(sum (transaction) / group_sum (transaction ) ) *100

I need to know the formatting syntax to show the results as a percent with 2 decimal places. The formula is within an answer, not a worksheet.

17replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Vicki - right now we don't have the ability to format a formula in an answer using the format specification, only in a worksheet.  However, if this formula is not going to be aggregated, then you can try 'concat(<formula>, "%")'.  Note that this converts it to a string, so you can't aggregate these values.  It's also possible that the values can combine if the rows aren't otherwise unique, because this essentially becomes a string attribute.  

    There is an open feature request to support answer level formatting, but it's not currently in the works.  Hope this helps.

    Reply Like
  • Hi Bill,

    You have confirmed my suspicions, and provides me with info to bring back to the DW team to have them put this sort of thing in the worksheets.

    -V

    Reply Like
  • If anyone does find they need to take this route, it is slightly more involved as a concat won't do an implicit transform to text (it won't let you select a number as an input). You need to take a couple of steps:

     

    1. Round to two decimal places using the round function:

    round((sum (transaction) / group_sum (transaction ) ) *100, 0.01)

    2. Convert this to a string:

    to_string(round((sum (transaction) / group_sum (transaction ) ) *100, 0.01))

    3. Stick the percent sign on the end:

    concat(to_string(round((sum (transaction) / group_sum (transaction ) ) *100, 0.01)),' %')

     

    Aggregating percentages never works out happily so the side effect of removing totals isn't too bad, as long as you bear in mind Bill's comment that this becomes another attribute (a dynamically generated one, but an attribute all the same). 

    Reply Like
  • Stuart Herd this still doesn't allow the user to set the precision/scale of the decimal.  This (string) solution would work if there were a LEFT() or RIGHT() function... but there isn't from what I can tell.

    Bill Back how do we see/review the list of Feature Requests?  I can't seem to find it anywhere.  Also, this feature could be solved with DECIMAL(x,y) or FORMAT() functions, similar to SQL... there is a TO_DOUBLE function, but it doesn't allow the user to set the precision/scale.

    Thanks for the response guys!

    Sam K.
    Tyson Foods

    Reply Like
  • Sam Kazery Have you been able to try the suggestion from Stuart Herd?  I believe you can control the precision/scale of the decimal with his approach by modifying the 0.01 to either 0.1/0.01/0.001/etc. The other caveats mentioned in the replies still apply. We will be at Tyson Corp HQ tomorrow for additional training sessions, so please feel free to stop by if you would like to discuss further. Thanks!

    Reply Like
  • Jason George  - Good to hear from you again!

    We actually met at Tyson today... and I'll be there tomorrow too.  I was the person who identified the undocumented use of "M" to auto-populate millions into the number format when adding a conditional format. :)

    Yes, I tried Stuart's suggestion before I replied above; but unfortunately it does not drop the trailing zero(s).  For example, when I round the value to 0.1, then a field that is formatted as ##0.00 with a value of 543.21 would simply round to 543.20... but there would still be 2 significant digits.

    I have successfully used the "to_integer" function to drop ALL decimal places, but that still leaves the user unable to modify the format to the desired scale.

    This obviously isn't a big deal, but it would add much more flexibility to users who exporting charts/tables while also preventing the maintenance of multiple fields/Worksheets just to provide various number formats. 

    Maybe we'll see this functionality in a future release soon.  Thanks for all your help today... I'm really liking what I am seeing of ThoughtSpot so far - great tool!

    Reply Like
  • I faced this issue too, and found that there is no way out to keep the same decimal places throughout. Even after removing the decimal places by changing the values to integer, when i convert them into graph mode, they switch back to have decimal places. It would be good to have this feature .

    Reply Like
  • I have not been able to get this to work in most instances. It's unfortunate that ThoughtSpot does not have native and robust formatting built in.

    Reply Like
  • Hi  David

    We've got you covered!! We have heard the need for formatting from our community, and in response, we have built something that I think you all will love. It is in test now and we will be demoing it at Beyond. We are targeting release by end of year, so hang on!

    Margie

    Reply Like 1
  • Margie Roginski sounds good! 

     

    Look forward to seeing it next week! 

    Reply Like 1
  • Marc Price and any of our other wonderful TS Community members, if you are coming to Beyond, please do introduce yourselves when you see me, I'd love to talk in person.

     

    Margie

    Reply Like
  • Margie Roginski I will be coming along to your presentation! 

    Reply Like
  • Hi  Margie Roginski , does "Advanced Chart Configuration" work with KPI?

    In my case, I need to format KPI as a %.

     

    Thanks for your help!

    Luca

    Reply Like 1
  • Hi Luca, Our chart configuration feature set is being staged across releases. In 6.0 it is available on all charts, and you will see it applied to KPI's and  tables (the "table view" associated with charts) in the first half of 2020. Thanks for reaching out!

    Margie

    Reply Like 1
  • Margie Roginski is v6. 0 available now? 

    Reply Like
  • Marc Price we are in the final release stages, and will released before end of month.

    Reply Like
  • Margie Roginski awesome! Look forward to it! 

    Reply Like
Like Follow
  • Status Answered
  • 2 days agoLast active
  • 17Replies
  • 400Views
  • 10 Following