Calculated Column returns as a string instead of a number value

Toad Data Point & Toad Intelligence Central
1

I thought that his was fixed in an earlier version of TDP, but this gremlin has struck me again. I have created a calculated column that is a very simple formula for Gross Margin Dollars ( table1.Revenue - table1.COGS ). I ran this query several times throughout the day, and now it has decided that the result will be string value instead of a numerical value. This means that when I send the results to a Pivot Grid, the column will NOT behave as expected with an aggregation…no summing, averaging, etc…just a zero. How can this be fixed?

Here is a screen shot showing how the values are left aligned (as one would expect for text/strings), instead of right-aligned.

2821.Capture.jpeg

Toad Data Point 4.3.0.718 (32 bit)

2

I copied the formula and used it to create a duplicate column. Ran the query again and it worked. But now there is another column misbehaving and the “create a duplicate column” technique did not work. Grrrrr…

3

I couldn’t seem to replicate it. Did you try formatting the field to a number by using the Number Format on the Value Field settings dialog?

4

That doesn’t seem to work. Here’s what I tried today:

Query column is calculating a decimal rounded to 4 places, but it comes into the query results tab as a string.

DecimalAsString_1.jpeg

Go to the Pivot & Chart Tab, add the column to the data area and drop in something for the rows. TDP sees that the data is string so it guesses that it should count the values. The count is correct, there are three values for 201711.

DecimalAsString_2.jpeg

Go into Value Field Settings, change the Summarize method from Count to Sum, and change the Number Format to Number (4 decimal places). Format changes, but it doesn’t really seem to know what to do with it.

DecimalAsString_3.jpeg

Now it gets more interesting… Change the Summarize to Min or Max and it works (while I would never really want to sum margin percent values, this is still an issue for other data…I would need to sum revenue, margin dollars, quantity sold, etc.)

DecimalAsString_4.jpeg

Change Number Format to Percent with 2 decimal places, and nothing changes. The pivot continues to display a four place decimal. Go into the Number Format to check the settings and TDP has not saved the percent setting…it’s back to General.

Unable to reproduce…I have been in tech support before and I do understand. It’s rather tricky to fix something that won’t break. :slight_smile: I don’t know what else to offer as evidence…sometimes this happens, most of the time it doesn’t.

5

Try multiplying the value 1.0000 * the rest of your formula, that sometimes forces the correct behavior as the data type is often determined by the first part of the formula. If it starts with . it might be assuming text (yes, pretty stupid but I have seen enough stupid things in many systems). Or try changing the number format to be 0.0000. Good luck, play with it, make it do what it is supposed to do even when it behaves badly.