1 2 Previous Next 15 Replies Latest reply on May 16, 2018 10:45 PM by lei.chen.0

    Have trouble collecting number of records

    Amy Sung

      I'm using tableau online.

       

      Trying to make a date and time filter for the histogram of data record.

       

      First of  all, tableau automatically use string for my date and time data.

      so I use formula to turn into date and time format.

      (original data format 2017-08-01 00:00:00)

      DATETIME(LEFT([日時],4)+"/"+MID([日時],6,2)+"/"+MID([日時],9,2)+"/"+MID([日時],12,2)+":"+MID([日時],15,2)+":"+MID([日時],18,2))

      Because I am going to count the number of records by each date and time for the histogram, and I expected to get the number of records return to 1 but failed.

       

      Anyone who has any clue would be a great help!

      Thanks

        • 1. Re: Have trouble collecting number of records
          lei.chen.0

          Hello Amy,

           

          Depends on the data, simply changing data type might work, instead of a long calculation.

           

          Amy Sung wrote:

           

          Because I am going to count the number of records by each date and time for the histogram, and I expected to get the number of records return to 1 but failed.

          What kind of failure did you get?

           

          Please provide some sample data or a sample workbook, so that more dedicated advice is possible.

           

           

          Regards

          Lei

          • 2. Re: Have trouble collecting number of records
            Amy Sung

            Hi Lei,

            I have tried your suggestion method, but it shows no selection as shown.

            change data type.jpg

             

             

            By the way, the original data of date is as below.

            date format.jpg

            so that I create a calculation field called "New date",

            new date.jpg

             

            but even it is date and time format,

            when it comes to "Number of records", it won't be 1.

            number of records.jpg

             

            It is "Tableau Online" not the "Tableau desktop", and I'm not sure if that is the problem and if there's any hint for this situation. 

            If there's any further suggestion it would be a great help.

             

            Thanks

            Amy

            • 3. Re: Have trouble collecting number of records
              lei.chen.0

              Hello Amy,

               

              Amy Sung wrote:

               

              It is "Tableau Online" not the "Tableau desktop", and I'm not sure if that is the problem and if there's any hint for this situation.

              Got it! You are using Web Authoring on Tableau Online instead of Tableau Desktop.

               

              The first question: change the data type of a filed

               

              I tried this on Tableau Online Web Authoring and couldn't find the data type setting menu.

              It seems that the onlinehelp Data Types only applies to: Tableau Desktop, so there might be a possibility that this function is not available on Web Authoring, which I'm not quite sure.

               

              Thus, your calculation using DATEPARSE is a good solution for this.

               

              The second question: the histogram

              Amy Sung wrote:

               

              Because I am going to count the number of records by each date and time for the histogram, and I expected to get the number of records return to 1 but failed.

              I didn't quite understand the expectation of the histogram and your data.

              Does it means that each of the date-time has only one record?

               

              According to Histogram - Wikipedia ,

              A histogram is an accurate representation of the distribution of numerical data

              Are you trying to plot the occurrence per unique date-time?

              If so, this is just a time-series trend.

               

               

              Regards

              Lei

              • 4. Re: Have trouble collecting number of records
                Amy Sung

                Hi Lei,

                 

                Here is a part of sample data, like you said it is a time-series data.

                What I am going to do is to make some calculation based on date,

                for example, counting how many time zero shows in the 4th column during a specific period.

                I will create a pie chart with data values 1 and 0, then a filter by date, so that I could see the differences between that's say the pie chart on period 1:12/2-3 and period 2:12/20-30.

                 

                So, if I can count the data simply by "number of records", it will be straightforward.

                 

                I am still new to Tableau and maybe still thinking like Excel, which is the only tool I've been using before, it would be really helpful for any idea that you generous share with.

                 

                Thanks,

                Amy

                • 5. Re: Have trouble collecting number of records
                  lei.chen.0

                  Hello Amy,

                   

                  Thanks for sharing the data sample.

                   

                  I created a sample workbook based on your description and my imagination of possible needs.

                   

                  Please check the details in the attached workbook and see if there are any hints for you .

                   

                   

                  Regards

                  Lei

                  • 6. Re: Have trouble collecting number of records
                    Amy Sung

                    Hi Lei,

                     

                    Maybe it's still uploading so I didn't see any attachment yet, but thank you!

                     

                    What I'm worried about is since I don't have the authority to access Tableau desktop, hope it's not a problem to access to your workbook.

                     

                    Sincerely,

                    Amy

                    • 7. Re: Have trouble collecting number of records
                      lei.chen.0

                      Hello Amy,

                       

                      Amy Sung wrote:

                       

                      Hi Lei,

                       

                      Maybe it's still uploading so I didn't see any attachment yet, but thank you!

                       

                      What I'm worried about is since I don't have the authority to access Tableau desktop, hope it's not a problem to access to your workbook.

                       

                      Sincerely,

                      Amy

                      Sorry that I forgot about the fact you don't have Tableau Desktop

                      Instead I attached a PDF file.

                       

                      You can find the attachment directly in the post Re: Have trouble collecting number of records .

                      If you browse the post from your inbox, the attachment might not be visible.

                       

                       

                      Regards

                      Lei

                      • 8. Re: Have trouble collecting number of records
                        Amy Sung

                        Hi Lei,

                         

                        I was replying through my inbox, now I saw the file you uploaded here, thanks.

                         

                        If it is possible, I would like to know how you do the calculation in detail.

                         

                        And actually I couldn't be able to see the original data as a spreadsheet, but I know the counting isn't right.

                         

                        There should be only one data at a time, so it's no right if it shows more than one.

                        If you have any idea what might have caused this, the problem might be solved.

                         

                        Sincerely,

                        Amy

                        • 9. Re: Have trouble collecting number of records
                          lei.chen.0

                          Hello Amy,

                           

                          I attached a zip file including the screenshot of the worksheet settings.

                          The calculations are using simple aggregations, and the histogram is created via "show me" menu.

                           

                          Amy Sung wrote:

                           

                          And actually I couldn't be able to see the original data as a spreadsheet, but I know the counting isn't right.

                           

                          Understanding your data is important.

                           

                          From your screenshot, it seems that there are multiple rows containing the same data.

                          The possibilities might be,

                           

                          - There are other fields with various values, such as [value1]

                            

                          日時sameTimevalue1value2
                          2017-08-01 21:00:001100200
                          2017-08-01 21:00:001200200

                           

                          - The rows are just duplicated.

                            

                          日時sameTime
                          2017-08-01 21:00:001
                          2017-08-01 21:00:001
                          2017-08-01 21:00:001

                           

                          By the way, does this case occur in your data?

                            

                          日時sameTime
                          2017-08-01 21:00:001
                          2017-08-01 21:00:000

                           

                           

                          Regards

                          Lei

                          • 10. Re: Have trouble collecting number of records
                            Amy Sung

                            Hi Lei,

                             

                            Yes, the 3rd case is also shown when I click to view data.

                            But I am not sure if it is the result of extraction, or simply because the raw data is.

                             

                            Besides, since data was public by somebody else, is there any other way to check if the cause is base on original data or extraction mistake?

                             

                            Sincerely,

                            Amy

                            • 11. Re: Have trouble collecting number of records
                              lei.chen.0

                              Hello Amy,

                               

                              Understanding of the data is the beginning of effective analysis.

                              Please try confirming with data owner.

                               

                              If this is difficult and you have to explore the data by yourself, then some basic checks could be like this,

                               

                              row numbers

                              distinct count of fields

                               

                              And try to identify the unique key

                               

                              無題.png

                               

                               

                              Regards

                              Lei

                              1 of 1 people found this helpful
                              • 12. Re: Have trouble collecting number of records
                                Amy Sung

                                Hi Lei,

                                 

                                It is basic but very important to me, thanks.

                                 

                                By the way, I wonder whether connect to file as a data source is available for Tableau online users.

                                I found no where to add a data source but connect to the data source which already exist.

                                 

                                Best regards,

                                Amy

                                • 13. Re: Have trouble collecting number of records
                                  lei.chen.0

                                  Amy Sung wrote:

                                   

                                  By the way, I wonder whether connect to file as a data source is available for Tableau online users.

                                  I found no where to add a data source but connect to the data source which already exist.

                                  Users with site role "Creator" can do such fascinating things on Web.

                                   

                                  Look at the gif image in Creators: Connect to Data on the Web

                                  And Creators: Prepare Data on the Web

                                   

                                  Please have a talk with your Tableau administrator in your company, to see if you can get the Creator site role

                                   

                                  Regards

                                  Lei

                                  1 of 1 people found this helpful
                                  • 14. Re: Have trouble collecting number of records
                                    Amy Sung

                                    Hi Lei,

                                     

                                    Checked the two links, thanks.

                                    I supposed it means Tableau Creators equals to Tableau desktop users.

                                    That means if there's no one could access Tableau desktop, no such Creator exists, is my understanding correct?

                                     

                                    Best regards,

                                    Amy

                                    1 2 Previous Next