6 Replies Latest reply on Mar 27, 2013 4:51 AM by Nick Bateman

    Weird one with a Yesterday filter

    Nick Bateman

      Have a strange issue ...

       

      Someone has written a dashboard with various sheets, all of which have a "Yesterday filter". The purpose of course is to deliver a daily report of what happened yesterday. It has been published to server and I am trying to email it out as a PDF file to that it is in peoples inboxes when they arive in the morning.

       

      The problem is that the "Yesterday filter" seems to have a mind of it's own. I have been running it hourly today (24/8/2012), and the results I have received are for:

       

      Time run:     Report for:     i.e.

      01:30          22/8/2012     2 days ago

      02:30          21/8/2012     3 days ago

      03:30          21/8/2012     3 days ago

      04:30          23/8/2012     YESTERDAY!

      05:30          20/8/2012     4 days ago

      06:30          22/8/2012     2 days ago

      07:30          22/8/2012     2 days ago

      08:30          21/8/2012     3 days ago

      09:30          19/8/2012     5 days ago

      10:30          22/8/2012     2 days ago

      11:30          22/8/2012     2 days ago

      12:30          21/8/2012     3 days ago

      13:30          18/8/2012     6 days ago

       

      There is a little bit of a pattern there i.e. I'm expecting the next three hours to deliver reports that are 2, 2, 3, and 7 days out - I'll let you know!

       

      Detail:

      - Version 6.5.1

      - Server on Windows 2008

      - I'm using the following commands to create the file

           & $BIN\tabcmd refreshextracts "DailyKPI" --synchronous --timeout 1200

           & $BIN\tabcmd get views/DailyKPI/$REPORT -f "$BIN\scripts\ReportImages\$REPORT" --timeout 1200

       

           executed through Powershell

       

      - Only the current pdf file is retained (it overwrites previous ones), and it IS created every time i.e. not sending old files

       

      Driving me mad. Any ideas? Please?

       

      Nick.

        • 1. Re: Weird one with a Yesterday filter
          Joe Mako

          We would need to see your workbook to troubleshoot, a packaged work, and details on your setup.

           

          My first thought is that either the formula/filter logic is not what you expect, or it could be a localization setting if it works as expected on desktop, but does not work when published to Server.

          • 2. Re: Weird one with a Yesterday filter
            Nick Bateman

            Thanks Joe.

             

            I've created a trimmed down version of the report with just one example of a problem sheet. This one just displays the Report date (which should be yesterday) - see attached. Currently this is working fine - it normally takes a few days to stop working (I did say it was weird).

             

            Not sure what you mean by a "packaged work"?

             

            Set-up - see items in initial post - what else do you need:

            - Backend DB is Netezza

             

            I've checked out the localisation settings on the server and all looks normal.

             

            Again, thanks for your post.

             

            Nick.

            • 3. Re: Weird one with a Yesterday filter
              Joe Mako

              sorry about my typo, I meant a packaged workbook a .twbx file. What you attached is just a workbook, the XML that defines that you want Tableau to do, there is no data in that file, so I cannot really open it.

               

              You will need to either extract some data, or make up some sample data that represents your situation and reproduces your issue. If your issue cannot be reproduced with an extract or sample data, then I will not be able to help, and you will want to contact Tableau support for assistance http://www.tableausoftware.com/support/request

              • 4. Re: Weird one with a Yesterday filter
                Nick Bateman

                OK, Thanks Joe. Haven't been ignoring this, it just had its priority squashed by other stuff.

                 

                Have attached the .twbx file, and possibly a clue... When extracting the data for the twbx, got the following message:

                tableau-1.png

                 

                I think this is down to the DB server being on UTC and the Tableau server on Local Daylight saving (1 hour difference between the two). Doesn't explain of course why it would return a different "Yesterday" at different points during the same day.

                 

                A quick update on the results I've been getting with the attached report:

                Ran this hourly today (6/9/2012), and it's reported Yesterday as being 2/9/2012 6 times, and 27/8/2012 5 times.

                 

                 

                Nick.

                • 5. Re: Weird one with a Yesterday filter
                  Joe Mako

                  yeah that seems very strange, it returns the correct yesterday for me today.

                   

                  Maybe it is because you have an old version of Tableau, see https://licensing.tableausoftware.com/esdalt/ for installing the latest version.

                   

                  I still say the best resource for you is Tableau Support, they could trace the logic in their code, or at least help you pinpoint the cause.

                  • 6. Re: Weird one with a Yesterday filter
                    Nick Bateman

                    The solution to this problem was to include the "?:refresh=yes" parameter to the workbook/view name.

                     

                    Specifically, with the assistance of Tableau support, we changed:

                         tabcmd export ReportDate/ReportDate --pdf -f c:\reports\ReportDate.pdf

                    to:

                         tabcmd export ReportDate/ReportDate?:refresh=yes --pdf -f c:\reports\ReportDate.pdf

                     

                    As expertly explained by Matt in tableau support:

                    "... the error was that the export command was using data from cache. This explains the odd behavior before where each hour a different result would appear. This was because the command was hitting a different cache each time. When a cache expired or did not exist, this would allow the query to be executed and 'yesterday' would finally evaluate correctly.

                    The resolution is to use a parameter at the end of the workbook/dashboard designation of ?:refresh=yes This invalidates the cache so that the export will not take any cached data and 'yesterday' should always evaluate correctly."

                     

                    Nick.