If then else formatting

I want to create an if then else column which displays either current revenue OR the number of metrics.

Problem is my IF then Else formula wouldn’t let me format the numbers! I want to keep the formatting of the original column if possible. (If I could format the new value itself that would also work)

Both the referenced columns are formatted correctly in the database.

This is a known issue with the if-then-else column.
For this particular use case, I’m not even sure if there is a good workaround.
The only thing I can think of immediately would be to use a JavaScript column. Which is way overkill, but would get the job done.

@Jeff_Hager any ideas?

Actually, I just had a thought. What you could do is use two template columns applied to the two original columns to “lock” the formatting, and then return either template column from the if-then-else column. This would be converting them to strings, but as long as you only need the result for display purposes, that should be fine.

1 Like

@byhazellim - here is what that might look like as a JavaScript column:

if (p1 === 'Metrics') {
  return p2;
}
else {
  let val = p3.toFixed(2);
  return `$${val}`;
}

I tried the JS version but don’t seem to be able to make it work!

Looks like you are using single quotes instead of backticks around $${val}

My first thought would be the template column method too. A little easier to understand compared to javascript.

I’m not sure if it would work, but I wonder if changing the IF column around, so it’s like this, would work.

If Model is 'Metrics' Then 'Customers'
Else If Model is not 'Metrics' Then 'Revenue'
Else blank

The theory is that, by including a string option in the IF, it will force the entire result to be a string, regardless of conditions. Not sure if it would still retain the formatting though, but worth a shot.

1 Like

Just fixed the JS! And the template formula version works too!

But neither keeps thousand separator comma though. How may I fix that please?

Need to modify the JavaScript a bit (this is probably better anyway):
Edit: I just noticed that you want it rounded, so have adjusted for that.

const formatter = new Intl.NumberFormat('en-US', {
  style: 'currency',
  currency: 'USD',
  maximumFractionDigits: 0
});

return p1 === 'Metrics' ? p2 : formatter.format(p3);

1 Like

Thank you Darren!!!