remove negative sign and replace parenthesis with 2 decimals

workflow has been developed with macro to split the city into different files. i need support to convert the output files values into which is now in negative sign to Parenthesis (within brackets) with decimals.

This should be -208.4519102 changed to (208.45). Also request to not suggest to create any new columns, which will disturb the workflow run, as we will remove the completed month for every month run.

Herewith attached the workflow and macro along with linked two excel file - one with data, second to use for split into different files.

City.xlsx

Summary File.xlsx

Test Macro.yxmc

Test_Workflow.yxmd

Reporting

Accepted answers

Luke_C

To add on to @MarqueeCrew 's solution, I don't think you even need the macro in the workflow. From what I can tell it's being used to filter on each city and render the reports separately, which can just be done using the grouping options in the reporting tools themselves.

Test_Workflow.yxmd

Luke_C

Hi @pdhanase

Update the formula in the tool to:

IIF([_CurrentField_]<0,"(" + replace(ToString([_CurrentField_],2,1),"-","") + ")",ToString([_CurrentField_],2,1))

For the formatting, not much I can do about that. This specific format you require means that we need to convert the fields to be strings in alteryx, so they are output as strings (text) in excel. You might consider not changing the format in alteryx and instead handling the formatting in excel.

All comments

MarqueeCrew

Hello @pdhanase ,

I'd use a Multi-Field Formula tool and create the transformation from a NUMERIC to a V_STRING for your amount fields. Please remember that these field conversions will make them strings and that there will be conversion warnings.

Select your NUMERIC fields (any/all that you want to change)

Uncheck the "copy output ..." option

Check the Change Output Type to V_String Size 64

Expression:

IIF([_CurrentField_]<0,"(" + ToString([_CurrentField_],2,1) + ")",ToString([_CurrentField_],2,1))

I added commas for thousands... If you don't want the commas, change both of the ToString() functions to ,2,0

Cheers,

Mark

ToString.yxmd

Luke_C

Test_Workflow.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335