1 2 3 Previous Next 36 Replies Latest reply on Aug 21, 2018 3:05 PM by Robertino Bonora

    Default Date Filter Value on Refresh

    Chris Cushman

      Quick question, I am stuck on how to make a date filter default to the most recent value when running a dashboard on a daily refresh. Therefore I can schedule the refresh and the data is always up to date when the users access the site but they have the option to look at old data via the filter. Am I missing a quick setting, or is it more complicated?

        • 1. Re: Default Date Filter Value on Refresh
          Richard Leeke

          There is a "Relative Date Filter" option - have you tried that?

           

          Drag a date onto the filter shelf and select "Relative date".  The default for that is "today" but there are various options.  If you show that as a quick filter you can choose between various relative date options - or you can also change the filter to a range filter or other options from the context menu on the quick filter.

           

          If those options don't meet your needs, post a fuller explanation of what you want and I'm sure Joe will come up with something ingenious.

          1 of 1 people found this helpful
          • 2. Re: Default Date Filter Value on Refresh
            Chris Cushman

            Hey Richard,

            Thanks for the reply.

            Let me clarify. Say my current workbook contains a date field (lets call it report date) with date values 1/1/2011 - 7/6/2011.

            I can add a filter to the view which I can then set to 7/6 and publish the report with a daily schedule refresh on the server tied to the updating data source.

            Everyday my back end data source will grow by 1 more day so tomorrow the workbook will have data up through 7/7.

            But when the refresh is complete and the user opens the workbook through the web, the filter will still be set to 7/6.

            I am looking for a way to default the filter to max date value so the workbook always has the latest data as the first thing that show up.

            From here the user could target a different date via the filter dropdown to look back into history.

            Make more sense now? I am suprised this is as complicated as I am making it. :) Should be simple.

            Chris

            • 3. Re: Default Date Filter Value on Refresh
              Richard Leeke

              I don't have server, so I don't really understand the issue.  Can you not just set the filter up as a relative date filter default to "Today" and publish that?

               

              You'll need someone who knows server to jump in here, I think...

              • 4. Re: Default Date Filter Value on Refresh
                James Baker

                So far I don't see anything server-specific here.  You'd see the same in Desktop if you hit "refresh" the next day.  Chris, have you found the Relative Date filter type yet?  When you drag a date onto the Filter shelf, it's at the top of the "How do you want to filter on [Field]?" list.

                • 5. Re: Default Date Filter Value on Refresh

                  Hi James,

                  The relative date filter is a work-around but requires some sacrifices for production reports.  It allows the default view to be up-to-date but does not allow as much specificity as other filter options (e.g., range-of-dates).

                   

                  I have the same problem as Cush and wish that I could get date filters to update with each refresh - this just seems like a forgotten feature.

                  • 6. Re: Default Date Filter Value on Refresh
                    James Baker

                    You can also use the TODAY() function in calculated fields if you want to define a more complex relative filter that doesn't fit into one of the six quick categories.

                    • 7. Re: Default Date Filter Value on Refresh
                      Chris Cushman

                      Ok, maybe I am not clearly articulating the problem.

                      Here is a step by step approach.

                      My data source has dates from 1/1 to today.

                      I build a viz and have a date filter in the dashboard that allows the user to specify any date in the date range.

                      I set the filter to be today (let's say 7/11)

                      Tomorrow my server refreshes the data connection and I have data through 7/12.

                      If I am a user and I log into the dashboard, I have a filter with all the dates from 1/1 to 7/12.

                      Because the dashboard was designed on 7/11 as the selected value in the filter, it automatically defaults to 7/11 as the selection.

                      Therefore in order to see 7/12, every user has to go to the filter and select 7/12 or any subsequent day to get the most up to date data.

                      My question is: is there a built in way to get the date filter to default to the maximum value, being the current day, or has someone found a work around for this.

                      I need my dashboard to have the most up to date data for the users displayed upon dashboard login...

                      BUT it must have the ability to allow the user the option to see all days in the data source.

                      Does this help clarify?

                      Thanks,

                      Chris

                      • 8. Re: Default Date Filter Value on Refresh
                        Zach Leber

                        Here's my workaround, but I'd much rather use the date range filter that lets users set both the start and end date, but have the end date default to TODAY() and the start date default to TODAY()-X where I can decide on a default X.  Setting the range filter to use all values in the DB does get the end date correct, but the start date is then much earlier than I would want for the default.  And the relative date filters only allow the users to set start or end but not both.  And using parameters for dates doesn't work because you can't initialize parameters to TODAY().  But there is another way:

                         

                        1) create an integer parameter called Date Window, default = 30 (for the last 30 days)

                        2) create an integer parameter called Date Offset, default = 0 (to show data relative to Today)

                        3) filter your date field using Condition / By Formula, to something like

                        [Your Date] >= (TODAY()-[Date Offset]-[Date Window]) AND [Your Date] <= (TODAY()-[Date Offset])

                         

                        Then by default you will show the last 30 days of data.  Change Date Window to show more or less, change Date Offset to go back in time.

                         

                        I'd love to hear any better suggestions.

                        3 of 3 people found this helpful
                        • 9. Re: Default Date Filter Value on Refresh
                          Richard Leeke

                          I played around with an example using a similar approach last week, but it seems I never got around to posting it.  I was experimenting with different options for how you specify the range and the end date.  I wouldn't actually suggest doing it exactly as I've got it in the example, but I thought I'd post it in case it sparks any useful ideas.

                          • 10. Re: Default Date Filter Value on Refresh
                            James Eichinger

                            I attempted to get around this by creating a calculated field that was simply MAX(RUNDATE). My intention was to then drop this into a filter so everything but this date would be excluded. However, this doesn't work because I can't filter on a discrete measure, and if I convert it to continuous, all the values of RUNDATE appear in the filter. I'm shocked that getting a filter to default to the most recent date is so difficult.

                            • 11. Re: Default Date Filter Value on Refresh
                              guest contributor

                              If you create on the datasource side an additional field max_portfoliodate = to get the latest date this will be in your dataextract and then create a calculated field

                              if max_portfoliodate = portfoliodate then " most recent day" else portfoliodate end

                              Then you can filter on the new calculated field and check off most recent day.

                              • 12. Re: Default Date Filter Value on Refresh
                                guest contributor

                                Hi all,

                                 

                                I have researched a lot on this issue (calculated fields/relative date filter...). I have dashboards published against SQL Server databases (extracts refreshed every day on server) . My users want the filter to default to current week every time they open the dashboard on the sever.

                                 

                                Relative date filter is not an option for me, i need to physically select particular week date in filter as needed. Also my week runs from Mon - Sun & not Sun - Sat.

                                 

                                I am providing my solution for this. Hope this helps someone.

                                 

                                Filter Type I use is Compact List

                                Note that Tableau defaults to the selected field (Week Start Date in my case) when published to the server. Even tough the extracts are refreshed, Date field remains at the one which was used during publishing.

                                 

                                So i have decided to create a static field value for the Current Week which never changes.

                                 

                                So i have a calculated field in my SQl,

                                 

                                CASE WHEN [WeekStartDate]= (Logic to get Current Week Start Date)  THEN 'Current Week'

                                        ELSE CONVERT(VARCHAR(15),WeekStartDate) END AS WeekStartDate

                                 

                                I use this calculated WeekStartDate field as filter on my dashboard, with value 'Current Week' selected.

                                So every time the data is refreshed, new weeks records will have 'Current Week' as the value for WeekStartDate.

                                Dashboard also defaults to WeekStartDate filter value 'CurrentWeek'.

                                 

                                So Dashboard always shows data for 'Current Week', & users can select past week dates from the filter if they need to.

                                 

                                Note: I Sort the new Calculated field WeekStartDate in descending order. (Right click on field name in Dimensions shelf->Field Properties->Sort ->select Sort Order as descending & sort by Data Source Order)

                                 

                                --

                                Raghu Guggilam

                                3 of 3 people found this helpful
                                • 13. Re: Default Date Filter Value on Refresh
                                  guest contributor

                                  Hi all,

                                   

                                  I have researched a lot on this issue (calculated fields/relative date filter...). I have dashboards published against SQL Server databases (extracts refreshed every day on server) . My users want the filter to default to current week every time they open the dashboard on the sever.

                                   

                                  Relative date filter is not an option for me, i need to physically select particular week date in filter as needed. Also my week runs from Mon - Sun & not Sun - Sat.

                                   

                                  I am providing my solution for this. Hope this helps someone.

                                   

                                  Filter Type I use is Compact List

                                  Note that Tableau defaults to the selected field (Week Start Date in my case) when published to the server. Even tough the extracts are refreshed, Date field remains at the one which was used during publishing.

                                   

                                  So i have decided to make a static field value for Current Week which never changes.

                                   

                                  Here's the  calculated field used in my SQl query to do this,

                                   

                                  CASE WHEN [WeekStartDate]= (Logic to get Current Week Start Date)  THEN 'Current Week'

                                          ELSE CONVERT(VARCHAR(15),WeekStartDate) END AS WeekStartDate</>

                                   

                                  I use this calculated WeekStartDate field as fitler on my dashboard, with value 'Current Week' selected.

                                  So every time the data is refreshed, new weeks records will have 'Current Week' as the value for WeekStartDate.

                                  Dashboard also defaults to WeekStartDate filter value 'CurrentWeek'.

                                   

                                  So Dashboard always shows data for 'Current Week', & users can select past week dates from the filter if they need to.

                                   

                                  Note: I Sort the new Calculated field WeekStartDate in descending order. (Right click on field name in Dimensions shelf->Field Properties->Sort ->select Sort Order as descending & sort by Data Source Order)

                                   

                                  --

                                  Raghu

                                  • 14. Re: Default Date Filter Value on Refresh
                                    Babu patnaik

                                    Hi Raghu,

                                     

                                    Hope every thing is going well.

                                     

                                    I am facing the same issue as you mentioned above. I want to update the default date value to the latest date that is today's date. I saw your solution above but I could not get the exact workout on my dashboard. If possible can you attach workbook with the sample solution so that it will be helpful for me.

                                     

                                    Thanks in advance

                                     

                                    Babu

                                    1 2 3 Previous Next