7 Replies Latest reply on Mar 15, 2016 9:20 PM by ruth.wyer

    Page default changing when extract is refreshed

    ruth.wyer

      My apologies in advance that I cannot upload my workbook (due to security). 

       

      I'm hoping someone may have come across this issue before.  I have created a workbook in 8.2 with a scheduled extract and published it to server.  There is a date on the page shelf with two settings "End date" which updates each time the extract refreshes and 'Start Date' which is a calculated field based on End Date - x weeks.   Everything is working fine except that I want the workbook to always default to the End date. Instead, when the extract refreshes, the server version defaults back to Start Date.  When I open the workbook itself, it has the settings as per when I published the workbook weeks ago. i.e. it does not match what is on server, as you would expect, it is as per when I published it (which has page set to End Date).  I can't see a way to force the report to stay on this date when the extract refreshes.

       

      My workbook is based loosely on this article, although my start and end dates are calculated, not parameters.

       

      https://www.interworks.com/blog/pmathewson/2014/11/10/tableau-viz-scatter-plot-twist-movement-plot

       

      Thanks in advance

      Ruth

        • 1. Re: Page default changing when extract is refreshed

          Hey Ruth,

           

          A few questions:

           

          1. Does the workbook default to the end date when first published?

          2. Does the end date remain when the extract is refreshed within Tableau Desktop?

          3. Does the end date remain if a live connection is used?

           

          Thanks,

           

          -Diego

          • 3. Re: Page default changing when extract is refreshed
            ruth.wyer

            Thanks for the nudge, Toby.  I didn’t receive a notification back in Dec that someone had replied!  My sincere apologies, Diego.  I can’t log in from this computer to reply on web so am hoping this reply via email will reach you.  I’m not in office today so will test your questions and get back to you.  Thanks for replying and my apologies again that I did not see your reply earlier.

             

            Thanks

            Ruth

            • 4. Re: Page default changing when extract is refreshed
              ruth.wyer

              Hi Toby,

               

              My apologies for the delay. We are on very old versions of IE and Chrome here and am having lots of trouble with websites, including this forum.  I have been able to test the first two questions, however, as the data source is only updated every week, and I have already refreshed at question 2, I will need to wait until next week to test question 3.

               

              1. Does the workbook default to the end date when first published?

              Yes

              2. Does the end date remain when the extract is refreshed within Tableau Desktop?

              No. reverts back to start date.

              3. Does the end date remain if a live connection is used?

              Will advise next week.

               

              Thanks

              Ruth

              • 5. Re: Page default changing when extract is refreshed
                ruth.wyer

                Just clarifying.  At Question 2 above, it will revert back to Start Date if it finds the end date has changed.  If I refresh again, and the end date has not changed, it will remain on End Date after the extract refresh has completed.

                • 6. Re: Page default changing when extract is refreshed
                  ruth.wyer

                  I'll see if I can create a simplified anonymised version that repliactes the problem and I can then upload. 

                  • 7. Re: Page default changing when extract is refreshed
                    ruth.wyer

                    OK.  Unfortunately, I can’t recreate as it turns out the workbook is using custom SQL and I can’t replicate that with an excel data source.  Also can’t convert to Live Connection as Tableau throws an error with my source data (doesn’t like something about the date format).  The source data is appended each week with the most recent week’s data for each dimension.  I need to know what the latest date is, however, the structure of the source data does not support having the most recent date as a field.  We are therefore doing it in the following custom SQL:

                     

                    SELECT

                    1. x.*,

                    MAX(x.WEEK_ENDING) OVER () AS Last_WEEK_ENDING  

                     

                    FROM

                    (

                    SELECT a.*

                    FROM <tablename> a

                    QUALIFY (((MAX(a.WEEK_ENDING) OVER() - Date'2014-01-04'+ 1) / 7) * 7 - 1+ Date'2014-01-04') >= a.WEEK_ENDING

                    ) x

                     

                    I then use the Last_WEEK_ENDING to create the Start Date using this calculation and a user defined parameter defaulted to 13 weeks:

                    DATEADD('day',-[user parameter]*7,[Last_WEEK_ENDING])

                     

                    I then have this calculation which allows sizing of start and end dates

                    IF [WEEK_ENDING] = [Last_WEEK_ENDING] then 2

                    elseif [WEEK_ENDING]= [Start Date] then 1

                    else 0

                    END

                     

                    And this calculation which is used as a page name and which allows me to put the Date in a readable format in the Title on the Dashboard. It is also used as the Size shelf allowing trail lines between start and end dates that display the movt over time.

                    IF [Change Over Time (parameter)] = 2 then left(str([Last_WEEK_ENDING]),10)

                    elseif [Change Over Time (parameter)] = 1 then left(str([Start Date]),10)

                    END

                     

                    I wouldn’t blame you for running a mile on this one.  I know how hard it is to help someone when you can’t see their workbook.

                     

                    Thanks,

                    Ruth