8 Replies Latest reply on Feb 14, 2020 9:13 PM by linda chen

    Forced into 0 after decimal point in Tableau?

    linda chen

      hi,

      I could not figure out how come the numbers I put into like 2858.725 in excel sheet  is becoming 2858.0 when connected to Tableau?  for testing purpose I made the exact same number with decimals for the first 2 rows to 2 different columns (2018 revenue, 2017 revenue)---why the 2017 revenue column show all 0 after decimal point while 2018 revenue column is not? as they are all formatted the same way. 

      What really strange is if one look at Data source tab, in the 2017 revenue column all numbers converted to 0 after decimal point, which is NOT like that in Excel sheet it connected with.

      What did I do wrong?

      the last one 1213.0 should be 1213.7

      number to 0.jpg

      I attach spreadsheet here.

       

      Thanks in advance.

        • 1. Re: Forced into 0 after decimal point in Tableau?
          Joe Oppelt

          You attached your workbook, not the data spreadsheet.

          |
          Tableau sees point-zero in that 2017 columns.  Can't tell you much beyond that from the workbook.

          • 2. Re: Forced into 0 after decimal point in Tableau?
            linda chen

            hi, Joe:

            I attach both workbook and spreadsheet.

             

            Thanks in advance for taking a look at my case.

             

            thanks again.

             

            ps. how to tell Tableau not to convert to 0 when getting excel sheet data into Tableau?

            • 3. Re: Forced into 0 after decimal point in Tableau?
              Ken Flerlage

              Your measures are set to use the following default number formatting.

              So, Tableau isn't dropping the decimals--it's just displaying them with only one decimal. If you want to show all the decimals, you can increase the number of decimals displayed.

              Note: Here's how you set the default number formatting:

              • 4. Re: Forced into 0 after decimal point in Tableau?
                Joe Oppelt

                Ken Flerlage  All the measures in the original workbook are set the same.  Yet one seems to be TRUNCATING the fractions to zero.

                 

                Linda -- I rebuilt a new data source with the excel file, and it behaves correctly for me.  I can only imagine that somehow you have changed that column to do what you're getting.  Right click on your data source and choose Edit Data source.  (If I do that, it is looking for your local file, so I can't see the details on that screen.)  But look at the attached.  Sheet 2 uses the new data source I made.  I Just straight-up hooked to the copy of your excel and let it do all the default things it wants to do.

                • 5. Re: Forced into 0 after decimal point in Tableau?
                  Joe Oppelt

                  Interestingly enough, when I tried to do Edit Data Source (and I got the pop-up to find your original file), I chose my Copy of... file.  Tableau was fine with that.

                   

                  So both data sources in my workbook now point to Copy of...

                   

                  And still the original AWTS_2018 shows the values for SC TO 2017 rev as point-zero, even in the edit data source screen.  But the same column for my new data source shows the proper values.

                   

                  (I hope I described that in a way you can understand what I did.)

                   

                  I don't get what I'm seeing.

                   

                  Let me suggest this.  On your side, create a new data source, and point it to the exact same excel file.


                  What happens when you do that?

                   

                  Some screen shots.

                   

                  First one.  When I'm looking at my "Copy of..." data source:

                   


                  And when I'm looking at your original data source, but it's now point to my very same excel file as my copy:

                   

                   

                   

                   

                  I am curious what you did to shove that "billion" in there.  I'll bet it has something to do with this.

                  • 6. Re: Forced into 0 after decimal point in Tableau?
                    Joe Oppelt

                    I think I found it:

                     

                     

                     

                    The second one is the setting for the problem column.  Change that to decimal like the other column.

                    • 7. Re: Forced into 0 after decimal point in Tableau?
                      Ken Flerlage

                      Ah okay, I think I misunderstood.

                      • 8. Re: Forced into 0 after decimal point in Tableau?
                        linda chen

                        hi, Joe:

                        Yes, you got it right---2018 uses Number (decimal), and 2017 uses Number (whole).

                         

                        Thank you so much for investigating it out for me (I failed for trying to figure out for hours!). I truly have no idea how it came that way because at Data Source tab, I only connect and bring in data from spreadsheet and never thought to modify data with (decimal) or (whole) there.

                         

                        Also beat me over how I shovel in " billion" there ---as you see in my spreadsheet, I don't put in "billion" there. I only got it just like Ken indicated ---using Default Properties..>Number format...(see Ken's first screenshot).  I have no idea how it ("billion") shows up at Data Source tab.

                         

                        So, thanks again, Joe.

                        and Ken as well for helping out. I'm sorry I didn't state my problem clearly.