Not really - several people have been calling for a built-in "FORMAT" function for just this reason.
Yes - the FORMAT() function would be a fantastic addition....
Until FORMAT() floats to the top of the wish list, depending on your back-end D/B, you may be able to use a RAWSQL expression to do it.
Richard, can you use RAWSQL inside of a calculated field? Would you be able to illustrate using the example I provided? The backend DB in that case is a Microsoft SQL server 2008
Scott, I believe you are also quite familiar with the RAWSQL function. I'll ask you the same question I asked Richard. Can I get to what I need using RAWSQL? Would you be able to show me an example? Thanks!
Briefly: yes you can embed RAWSQL inside a calc field, so you can have different calls for the IF and the ELSE part.
My SQL Server knowledge is a bit thin, but a quick Google shows that SQL Server has CONVERT(), ROUND(), FLOOR(), etc functions and one of the options for CONVERT is money, so I'm sure it's possible. I'll take a look later if you're still stuck.
Yes, I do need help :-(
I tried some RAWSQL expressions and although it formats to the number of decimals I request it returns wrong values. For example when I tried the following
IF [Loss Measure] = 'Loss Per Checking' Then
IF [Time Frame] = 'YTD' Then IF Right([TIME_GROUPING_YTD],4) = STR(DATEPART('year',DATEADD('year',-1, TODAY()))) Then RAWSQL_REAL("Round(%1,2)",[LPA_CKG_YTD_RB]) END END
ELSEIF [Loss Measure] = 'Total Loss Amount' Then
IF [Time Frame] = 'YTD' Then [LOSS_AMT_PREVIOUS_YTD]END
It affected every value in my table in apparently non logical ways...Here is the before and after RAWSQL:
Original value With RAWSQL Desired results
0.092125384 0.01 0.09
0.100406908 0.01 0.1
0.313415293 0.03 0.31
0.008342134 0 0.01
0.001664119 0 0
0.442903999 0.11 0.44
-0.096786412 -0.11 -0.1
0.142098535 0 0.14
0.794713796 0.19 0.79
0.028819998 0 0.03
0.096489362 0 0.1
0.027664909 0 0.03
0.291896026 0.01 0.29
0.043894486 0 0.04
0.142880899 0.05 0.14
Will take a look later - I do just happen to have SQL Server 2008 on my laptop...
I had a quick look at SQL Server. ROUND() seemed to work as expected for me, I couldn't reproduce your behaviour.
But back on your original question, I couldn't see any server functions that seemed to help here at all - so sorry, it looks as if I gave you a bad suggestion. Thinking about it, that makes perfect sense, the database wouldn't expect to be concerned about presentation issues, that's Tableau's job.
So the only way I can think of is to build horrible convoluted calculated field doing lots of string manipulation to generate the format you want. I really doubt it justifies the effort.
Pleaaaase Tableau give us a FORMAT() function!!!