Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Table Reporting Tool Currency Question

Bren312
8 - Asteroid

Hi everyone, I'm trying to use the Table reporting tool to show Region profits and highlight any negative values.  I'm hoping to show the negative values as Currency in a ($123,456.78) format but I'm not having much luck using the "$" prefix; the negative values are displayed in a $-123,456.78 format.

 

Hopefully that makes sense; I've attached a workflow with what I've currently got and a screenshot of what I'm hoping to accomplishment.  Also, I'm on version 2019.2.  Thanks everyone!

 

Bren

13 REPLIES 13
jamielaird
14 - Magnetar

Hi @Bren312 ,

 

You will need to format your amounts as a string and use that field in the table. Here's a formula that will work:

 

IF [Profit]<0 THEN "($"+ToString(ABS([Profit]),2,1)+")"
ELSE "$"+ToString([Profit],2,1)
ENDIF

jamielaird_0-1615313013299.png

 

Example workflow attached

apathetichell
19 - Altair

Edited your table to include row & column rules relating to when Profit is negative. It includes the parenthetical prefix and suffix and overrides the column general "$" suffix for the Profit column. - Just noticed you want to keep the $ - just change the prefix in column rules to read ($ and it will add a $. The Row Rule highlights the entire row when Profit is negative.

 

Does this work?

Bren312
8 - Asteroid

@apathetichell , this is soooo close!  The negative values are still showing up with the minus signs and no dollar signs.

Bren312
8 - Asteroid

Thanks @jamielaird ;that absolutely works but I'm still holding out hope there's a way to do it in the table tool.  I'm putting together training on the reporting tools and was hoping to avoid a conversion formula if at all possible.

apathetichell
19 - Altair

Yeah - I keep trying to get it to work in the formula section of the column rule but haven't been able to get it to parse out the "-" number... I'll keep working to see if I can figure it out..

Bren312
8 - Asteroid

@apathetichell  It's a pain in the neck, for sure.  I keep hoping I'm missing something obvious because it seems like it shouldn't take a weird hacky solution but here we are.  Much obliged!

apathetichell
19 - Altair

Ok - the XML says that when it says "Formula" it means "Style Formula" so my hypothesis is that that won't help. If you don't want to do a string conversion my solution would be to create two profit columns - one for testing and one for display (which would feature the absolute value of profit).  That would look something like this:

 

jamielaird
14 - Magnetar

You can do it with Formula Styles but it requires an understanding of CSS:

 

https://help.alteryx.com/current/designer/table-style-rules

 

This is an advanced option. When the basic style overrides are insufficient, you can create your own style overrides, by writing a formula which constructs a CSS-like style text. This formula is computed individually for each cell to which it applies, and can be very powerful. Use of this option requires a solid understanding of CSS styles and some knowledge of PCXML and Composer.

Some Common PCXML properties to use within formulas:

  • background-color: The background color of a table cell or row.
  • decimal-places: The amount of numbers that appear after a decimal point for a numeric value.
  • postfix: Text that directly follows a value.
  • prefix: Text that precedes a value.
  • background-image: Sets an image as the background
  • color: Foreground color of the object.
  • border: Defines the borders around an element.
  • padding: The space between the element border and the element content.
  • font-weight: Sets how thick or thin characters in text should be displayed.
  • font-style: Sets the style of a font.
  • text-align: Aligns the text in an element.
  • white-space: Declares how whitespace inside the element is handled: the 'normal' way (where whitespace is collapsed), as 'pre' (which behaves like the 'PRE' element in HTML), or as 'nowrap' (where wrapping is done only through BR elements).
  • text-decoration: The decoration of the text such as overline, underline, line-through, none, or blink.
Bren312
8 - Asteroid

@jamielaird @apathetichell You guys are awesome; thanks so much for all your time and help!  At the end of the day, I ended up flagging the initial solution from @jamielaird as it was the one that gave me exactly what I wanted (even if it wasn't how I was hoping to get it).  Thanks again for everything guys!

 

Bren

Labels
Top Solution Authors