9 Replies Latest reply on Jul 30, 2018 6:04 AM by Stuart Fieldhouse

    Problem getting data from dataset

    Jure Kocbek

      Hello.

      I started using Tableau 2 weeks ago and I'm still not familiar with a lot of things.

      I have one dataset which contains all necessary data (fields: location, year, month, day, number of passes)

       

      How can I get a number of passes for the full year current and previous to make a comparison? Numbers does not match when I do the calculation

       

      I trieded:

       

      (1). (attached picture)

           1. In the initial query of connection I selected all data into temp table (#tmp),

           2. I added custom sql query to select complete #tmp,

           3. I added custom sql query 2 and left it by location, year, month, day,

           4. and added custom sql query 3 adn left joins it by location, year - 1, month, day,

       

      (2).

           1. In the initial query of connection I selected all data into temp table (#tmp),

           2. I added custom sql query to select complete #tmp,

           3. I dragged the location, the number of passes to the workbook and the added filter year,

           4. I created calculated field ({fixed location, year-1: sum ([num of passes])) and dragged it to workbook

       

      Because the dataset contains all the data I need, and I think that (1) is not necessary because it requires a lot of time for the same data i get from (2). I'd like to use calculated fields and parameters if it's possible.

       

      So all I need are passes for single location by current and last year.

       

      Thanks for help

      Best regards, Jure

        • 1. Re: Problem getting data from dataset
          kumar bharat

          Hi Jure,

          I am not sure why u r using temp table here.check this link to use temp table in custom sql

          https://community.tableau.com/docs/DOC-7933

          Run Initial SQL

          BR,

          bharat

          • 2. Re: Problem getting data from dataset
            Jure Kocbek

            Hello Kumar,

             

            in my situation it's not important how I run initial query. The main problem is that I have all the data I need in Tableau and I don't know how to get it out to build a propper table or graph. I'd like to get 3 columns (location, passes by current year, passes by last year). I know how to get data for current year but I have problem to do it side by side with last year.

             

            Thanks and best regards

            • 4. Re: Problem getting data from dataset
              Jure Kocbek

              Hi Kumar.

               

              Thanks for those links. I read it all but it's not what I need. Maybe I used wrong therm "Side by side", but here is an example what I need.

               

              Sql returns table like this:

               

              Location| Year | Month | Day | No. of passes

              100          2015      3      10       20

              100          2015      4       2        25

              200          2015      3       8        36

              100          2016      3      10       22

              300          2016      7       6        26

              100          2017      3      10       26

              300          2015      7       6        21

               

              I'd like to get a text table (not even a graph) which would group passes by location and selected year. Year would be dimension and used in filter. The expected result would be like this:

               

              Location | Passes in 2016 (Selected year by filter) | Passes in 2015 (previous year)

              100                        22                                                        45

              200                         0                                                         36

              300                        26                                                        21

               

              I hope I'm clear enough now.

               

              Thanks for the effort.

               

              Best regards,

              Jure

              • 6. Re: Problem getting data from dataset
                Jure Kocbek

                Hello Kumar,

                 

                still not what I need. From table below I know how to get passes from current year but I don't know how to get current and last years passes at once. This is the main problem.

                Thanks,

                 

                Best regards

                 

                Location| Year | Month | Day | No. of passes

                100          2015      3      10       20

                100          2015      4       2        25

                200          2015      3       8        36

                100          2016      3      10       22

                300          2016      7       6        26

                100          2017      3      10       26

                300          2015      7       6        21

                • 7. Re: Problem getting data from dataset
                  Stuart Fieldhouse

                  Hi Jure,

                   

                  The problem is that you're using current year as a filter on the data - as soon as the filter is applied then the previous year values are filtered out of the dataset and are therefore not available to you.

                   

                  An alternative is to use a parameter to select the current year - either as a pick-from-list or just as a value the user enters. Call the Parameter Current Year.  Then create two calculated fields:

                   

                  Current Year Passes:

                  if [Year] = [Current Year] then [No.of Passes] ELSE 0 END

                   

                  Previous Year Passes:

                  if [Year] = [Current Year] - 1 then [No.of Passes] ELSE 0 END

                   

                  Because the parameter doesn't filter out any of your data, both years are available to you - just use the two calculated fields on your view.

                   

                  HTH

                   

                  Stuart

                  • 8. Re: Problem getting data from dataset
                    Jure Kocbek

                    Great explanation.

                     

                    All I need now is custom filter where user can select current year and tableau will show data for selected year and a year before.

                     

                    Thanks and best regards,

                    Jure

                    • 9. Re: Problem getting data from dataset
                      Stuart Fieldhouse

                      Thanks Jure - remember - don't use a filter, use a parameter.

                       

                      S