This appears to be a database connection issue, unrelated to the calculation you're doing in Tableau.
Can you check your connections.
Are you using a live connection to your database? If so try using an extract
I mocked up something similar and can do the calculations without any issues.
Many thanks for replying.
I need the connection to be 'live' as data is polled periodically throughout the day.
Is there any other workaround, other than having to manually extract the data throughout the day, which would be counter-productive?
Many thanks again.
I am also using a live connection in my example, so I think the issue lies with your database
Have you connected to this database before? Can you connect to other tables, or perform other actions on it without getting the "reconnecting" error?
How much data are you working with?
Yes, I have been connecting to the database without any issues in the past and have multiple dashboards feeding from it.
However, I have spotted the following error whilst testing:-
ERROR CONVERTING DATA TYPE VARCHAR TO FLOAT.
Not sure what this means.
Are you doing the type conversion in your database or in Tableau? Because that is SQL error, unrelated to Tableau
What is the column type of your data in your db table?
Are you connecting via a custom sql query?
The error occurs because you are trying convert a letter to a numeric float value. I believe REGEX is available for SQL databases that support REGEX, SQL Server does not while Oracle does. If you create a Tableau data extract I believe you can use REGEX as well
I'm not sure there is a great Tableau based solution for this, all I can think of is 26 contains statements to filter out all the numeric characters. If you are using SQL server you could try to identify the records in a view or perhaps with Tableau custom SQL using the PATINDEX function.