6 Replies Latest reply on Apr 22, 2018 11:28 PM by Paula Roigés

    Visualization problems with PostgreSQL DB

    Paula Roigés

      Hello,

       

      I'm a beginner working with Tableau (please bear it in mind). I have done a connection to a real-time DB through PostgreSQL and my objective is to visualize the daily and monthly changes of different categories, showing the count of each them.

      db18.png

      Above, you could see a screenshot of the DB, which shows 8 new rows for April 18th, 2018. Having that, what I expected to see at the following graph (I don't know if you see it, sorry!) is a count per day for each category. Instead, what I can visualize is a rare counting, per instance, "Marketing digital" -Digital marketing-, as user id 9342, has 30 counted records on April 18th, which is impossible, because the DB only have 8 new records. What am I doing wrong here?

      viz1.png

      The other issue that I am encountering is here when there is no record, I expected the line to drop down to 0, instead, it disappears, leaving a blank gap. How can I solve it?

      problemviz2.png

       

      Thank you so much in advance!

      ------

      Tableau Desktop Professional Edition 10.5.2

        • 1. Re: Visualization problems with PostgreSQL DB
          Mark Holtz

          I've run into problems with PostgreSQL and the Timestamp with Time Zone Offset data type.

          If I recall correctly, Tableau doesn't recognize the "conversion" of date-times by their time zone offsets, and so it is representing all of your timestamps as UTC (thus advancing the timestamps by a number of hours if you are in a UTC - timezone).

           

          If you create a calculated field as DATEADD('hour',-5,[createdAt]) inside Tableau, I think it'll adjust things correctly. (I guess at your time zone, but use -# for however many hours you need to. UTC -5 is Central Time US, I think, I can never remember with the darn Daylight Savings).

          • 2. Re: Visualization problems with PostgreSQL DB
            Paula Roigés

            Hi Mark,

             

            thank you for answering, it seems more accurate, however, the count it is not correct, it shows more records per day that the one in PostgreSQL... Could it be that am I not adding the meseasure properly?

            On the other hand, I have identified the source of my second problem, the line does no decrease down to 0 because there is no record that day.

            Than you again.

            • 3. Re: Visualization problems with PostgreSQL DB
              Mark Holtz

              What time zone are you in?

              You will need to offset from UTC by the proper figure. -5 might not do it correctly in your case.

               

              That is controlling the counts, so do not try to assess the counts until you are confident you have Tableau representing records with the proper date and time values.

               

              For example, records that are actually supposed to show 4/18/2018 20:00:00 when offset -5 hours from UTC would actually show as 4/19/2018 01:00:00 in Tableau, despite the fact that a query in a tool directly attached to the database (PGAdmin or Toad for example) would show 4/18/2018 20:00:00 as expected.

               

              So in essence, you need to build a special field just in Tableau because it doesn't respect the UTC offset factor. That allows you to force the 4/19/2018 01:00:00 date in Tableau to be represented as 4/18/2018 20:00:00, which then, when rendered on a display would properly group that record to the date 4/18/2018 instead of incorrectly showing it on 4/19/2018.

               

              That is what I suspect is going on with your counts...

              If this is still not clear, I'd ask that you post a packaged workbook with at least some sample dates.

              • 4. Re: Visualization problems with PostgreSQL DB
                Paula Roigés

                The app receives data from different places, but I need to focus on Mexico. For the time zone, I'm in Barcelona, Spain (UTC +2, CEST, Madrid).

                I have attached you a workbook example. There are 4 tables, where 1 user can have many cards; 1 card has 1 category, and 1 category can have many professions.

                 

                Thank you!

                • 5. Re: Visualization problems with PostgreSQL DB
                  Mark Holtz

                  I'm afraid we might not get there unless you can provide a packaged workbook (.twbx)

                   

                  Maybe there is an issue with joins if you are involving multiple tables.

                  You said there are 8 records for ID 9342, but it represents a count of 30 on the display. On what table? Is there something that might cause that number to multiply when attaching those 8 rows to another table?

                  The timezone with offset might be causing some problems, but also there could be something else at play.

                  • 6. Re: Visualization problems with PostgreSQL DB
                    Paula Roigés

                    Hi, I'm going to check again the joins. Unfortunately, I cannot provide a packaged workbook, thank you anyway.