B4a Format Number

1 view

Skip to first unread message

Barb Magario

unread,

Aug 3, 2024, 5:30:59 PM8/3/24

to diztipiran

Currently the amount from the spreadsheet is displayed in currency format. I think I need to initialize a variable and re-format that but when I tried to do that is says it needs to be outside of the loop? I'm new to this so any help would be appreciated!

@tiffany initialize a string variable immediately after the Recurrence trigger. Then in the apply to each control you can add an append to string variable and then a compose to format the value in the string variable. Then use the output of that later in your email. But one thing you need to watch is that it looks like you are sending an email to everyone in the spreadsheet for each item every time the flow triggers - unless of course you are using a filter query.

I've attached my current workflow. Obviously the attached workflow is not working but any ideas would be greatly appreciated. Also if I'm doing this the long way - any thoughts on what would tool would be faster?

As regards a different way of doing it, what you're doing is fine, but a multi-field formula tool removes the need to type out multiple formulae in the same tool, that are essentially the same, so just reduces typing errors really.

Thank you so much for your quick response and suggestions. Each one of you were able to answer my question and also showing me there is more than 1 way of doing something in Alteryx. All the suggestions worked perfectly! Thank you.

These examples didn't start with a number in the thousands that didn't have a comma separator initially and successfully add one at the end. I am applying these functions and it is not adding in a thousands comma separator.

I have a description field, and would like to generate it based on values. I could not find any way to format numbers, like there is DATE_FORMAT for dates. The TEXT function in Google Sheets allows you to format exactly like you want it.

Not sure if this is of any help, but you can format numbers in a number field and then concatenate these fields with other text fields, assuming you are wanting the number to be part of a larger string of text. This obviously assumes that the numbers are always inserted in the same part of the text. Guessing you will have already thought of this, but just thought I'd mention it.

Alternatively, you could manybe setup an automation that watches a text field then runs a script, like below, which outputs to a Formatted text field.

Hi Dominic. Thanks for your reply. Using an automation + script for something this simple sound like an overkill. Plus I don't want to be possibly hitting my API limits because of something which should be so basic.

I'm not sure what you mean exactly by "you can format numbers in a number field and then concatenate these fields with other text fields". However, if I have a Number field formatted as Decimal with 0.00 precision, if the value is say 213.30, if you concatenate it in a formula generating a text, the output value is 213.3 not "213.30", as expected. This is a big pain for consistency in IDs.

Thanks for the detail on your use case. It helps to have a little more context. The script solution is more appropriate when you have long form text with randomly places numbers throughout that need formatting.

If you wanted to format a number to a string and preserve the two decimal places, you could maybe also use a formula like:

The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).

Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Displays date and time serial numbers as time values, according to the type and locale (location) that you specify. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Allows you to modify a copy of an existing number format code. Use this format to create a custom number format that is added to the list of number format codes. You can add between 200 and 250 custom number formats, depending on the language version of Excel that is installed on your computer. For more information about custom formats, see Create or delete a custom number format.

This example shows some of the variations in localized number formats. In order to get the format of the language used in the user interface of your application, make sure to specify that language (and possibly some fallback languages) using the locales argument:

The num function formats the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the number format set in the operating system is used.

for the second parameter - the format code - a tip I was given and which works is to go to the chart->properties->number dialgue and copy preferred format with cut and paste it into your num function. The format goes between simple quotes.

Could someone PLEASE HELP ME report the following. Except for what I say below, I cannot send an email to Jason Long, whenever I click send it just tells me to enter the name of the recipient, which it then just ignores again. Please someone forward this to Jason Long or support or anyone who can help me, I cannot do anything on this site!

I'm having big problems with the site - All of the dropdown-links at the top of the page (New, Your Stuff, History, Browse) do not work - at the bottom of the screen the path shows javascript:void(0) and nothing happends when I click teh links. The same thing for replying on threads. I can start a new one, but then I can only reply to it by replying directly to the email notification.

Meantime, you can use the round function (ref). Which is less than perfect, but is functional. If you need to have a particular number of sig figs you can use THE POWER OF MATHS! and do something like:

I've had endless trouble with decimals in XSLT/XPath 1.0, often a combination of it representing decimals as floating-point numbers and it using half-even rounding (banker's rounding). Unfortunately, the round(yournum*100) div 100 approach didn't work for me, due to floating-point imprecision. For example, multiplying 1.255 by 100 gives 125.49999999999999 (this isn't meant to be implementation dependent, as it's supposed to be IEEE 754, but I don't know if all implementations do adhere to that). When rounded, this then gives 125, rather than the desired 126.

I've taken the following approach, which I think works (although this is always a tricky area so I won't declare too much confidence!). However, it depends on your XSLT engine supporting EXSLT extensions. It presumes you want to round to two decimal places.

How do I format a number to look like this: 9,000my database field is in money data type, when I pull it up I see it like this: 9000.0000 that don't look right to me (I would like it to look like a real money format)

In a column I have whole number as decimal numbers too. I need that in a matrix, the whole numbers were displayed without decimals, and decimal numbers with decimals. Is is posible through any custom format ?

Thanks, I have read that doc previous to my post, but I couldn't find a combination that suits for my need. Is there anyone ? I need that my custom format works in a little differente way for whole numbers or decimal numbers.

I know we can change number formats to control how many decimals show for any given measure, but it doesn't look like we can change that format on different visuals. For example - in one visual I might want my measure to be Currency with 2 decimal points (in a table, for example). But in another one (such as a card visual or a tooltip for a bar chart), I want it with zero decimal points. I could create a second measure and have one with decimals and one without, but that's a real waste to have to duplicate things that way (and maintain them!).

@Vvelarde The option for changing the Value decimal places in the Field formatting section is grayed out. Eveni f I change the display units to Thousands, the Value decimal places is still grayed out. Nor do I see any options in the toolbars... You can see in the screenshot below that when I formatted the measure to General, it looks horrible now - it has a mixed number of decimals and there is no thousands separator. I've been hunting, but I can't find anywhere that I could change the number format to add the comma and change it to force two decimals for this one table. What am I missing? Is there a setting somewhere else that I'm overlooking?

@Vvelarde WOW! I'm floored! When that box was grayed out, I assumed it meant it wasn't editable. That's pretty standard functionality over the last... what, 15 years? But you were totally right - I just tried it, and it works perfectly to just type a number in there.