No, extracts are pulling data out of a database, spreadsheet, etc. and moving it into a Tableau extract. Generally this improves performance. If your data source is MS Excel or Access, which don't support COUNTD() and few other functions, doing an extract moves the data from using the MS Jet Data Engine to using Tableau's native Data Engine.
While you gain some functions, you lose others, namely RAWSQL.
I have one last bit of bad news for you. The LOOKUP() function Joe was referring to above, no longer works. It was a 'bug' that Tableau corrected in release 8.0.? (don't remember which one).
As to the negative sign, you could write a calc like this:
IF SUM([Sales])<0 THEN "-" ELSE "" END
The ''" will be completely blank.
We just updated to v8, so now the lookup function doesn't work. However when I try the rawsql_str examples you provided I get a "FORMAT": invalid identifier error.
What is causing this? Is there another way to resolve adding commas for a number that will be turned into a string?
Elizabeth, first I'm going to assume you are not using an extract (if you are, that's the reason for the error). If you are not using an extract, then copy and paste the RAWSQL_STR() function that is giving you the error.
Finally, working out the string manipulation to inset comma separators is involved and tedious. Before I take this on, I'll need to know what your MAX() & MIN() values will be. Or at least how many digits and decimals will be in your end result.
Here's the snippet of code.
IF ([E Filter] < 0 AND [Type] = '$')
THEN '('+ RAWSQL_STR("FORMAT(%1,'currency')",ABS(ROUND(FLOAT([E Filter]),0))) + ')'
ELSEIF [Type]='%' THEN STR(ROUND(FLOAT([E Filter]*100),2))+'%'
ELSE '$'+STR(LOOKUP(ROUND(FLOAT([E Filter])),0))
ABS(ROUND(FLOAT([E Filter]),0))) may be a number from 1 to 11+ digits. Since I round it, at most it should be 11 or 12 digits long, with no decimals.
Elizabeth, what's in [E Filter]? Is it a parameter? A calculated field with an aggregation applied?
Containing what calculation?
[E FIlter] is an calculated field that uses aggregated values. It is a field that contains a case statement, since I want the sheet to populate with relevant values based on the users choice with a parameter. Each of the branches of the case statement are sums of various measure values.
Would being connected to an Oracle DB affect anything?
Change this: '('+ RAWSQL_STR("FORMAT(%1,'currency')",ABS(ROUND(FLOAT([E Filter]),0))) + ')'
To this: RAWSQL_STR("FORMAT(%1,'$###,###,###,###')",[E Filter])
Or this: '(' + RAWSQL_STR("FORMAT(%1,'$###,###,###,###')",[E Filter]) + ')'
Change this: STR(ROUND(FLOAT([E Filter]*100),2))+'%'
To this: STR(ROUND([E Filter]*100,2))+'%'
Also change this: '$'+STR(LOOKUP(ROUND(FLOAT([E Filter])),0))
Maybe to this: RAWSQL_STR("FORMAT(%1,'$###,###,###,###')",[E Filter])
You don't need the FLOAT(). I'm also not sure why you're using the LOOKUP() since it no longer works for formatting. When you were trying to use ROUND() on a 'currency' RAWSQL_STR you were sending mixed messages. 'currency' formatting by definition has two decimals. When we use the # symbol we're saying show a digit here if one exists.
After making the changes, I still receive the same error message:
Oracle database error 904: ORA-00904: "FORMAT": invalid identifier
Sorry, no ideas.
I believe it's because format() is a MS function. If you're using Oracle, RAWSQL_STR() is trying to pass a string format function to the DB that Oracle doesn't recognize.
Refer to the link below for handling string formatting in Oracle:
I'm trying to use this as well to format a dimension that has multiple values in it - some percent, some number. I have a field called "type" and it has a 1 for number and a 2 for percent. I created a calculated field to add a percent sign to the fields that were percent and multiply by 100, or else just display the number. I'd like 2 decimal places, hence I added the "round" but that doesn't seem to be working - I'm still getting 8+ decimal places on some fields.
IIF ([CalculationType]=2, (str(ROUND([Rate]*100,2))+'%'),str(ROUND([Rate],2)) )
I've had Tableau a total of 2 weeks so I'm still feeling it out and trying to find all the information I can via the forums, but nothing seems to answer my particular question.
I added a workbook that demonstrates some number formatting inside calculations--hope this can help you:
If you cannot find the answer, I'd suggest opening a new thread with your specific scenario/question, along with a packaged workbook demonstrating the issue--since this question has been marked as "answered", you'll be much more likely to get a good response in a new thread with a workbook attached. Cheers!
Thank you! I'll check out the workbook and if no luck, I'll start a new thread.
STR(ROUND(some aggregate)) has a bug for some data sources where it will
return all those extra decimals (it's not actually rounding to the number,
if you extend out to 16 digits you'll see a .999999999999999 or a
.000000000000001 kind of thing. The solution I got from Tableau tech
support was to do something like ROUND(IF > 0 THEN +0.000001
ELSE -0.000001 END,0), or ROUND(+0.000001,n) .
An alternative that can be used in some cases is to return two separate
measures that return Null or a value, put them both on the Label/Text or
Level of Detail Shelf, and use the Default Number formatting for each to
get them to work.
On Tue, Nov 26, 2013 at 11:23 AM, Stacey Rothchild <