Table Reporting Tool Currency Question

[Bren312]

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

[jamielaird · Accepted solution]

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

Example workflow attached

[apathetichell]

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]

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

[Bren312]

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]

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]

@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 · Accepted solution]

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 · Accepted solution]

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]

@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