3 Replies Latest reply on Nov 23, 2016 8:03 AM by Andrew Watson

    Dynamically changing 'date' filter

    Marcus Bright

      Hi Tableau Community,

       

      Relatively new to Tableau so please bare with me if my query has a straightforward solution!

       

      I have an issue at the moment where i need to have an activity month filter showing on my dashboard.

       

      The date column in question is actually a simple integer based on month by month submissions we receive since we starting recording the information, rather than actual dates (1,2,3,4 etc.)

       

      The source data is updated once a month with a new activity month added in each time - my aim is to have the dashboard automatically update the pre-selected month to the latest month when the data is refreshed - with the option to switch to previous months.

       

      My attempts so far have been unsuccessful - I've tried to use a combination of parameters but ran into the issue of them having a static 'Current' value.

       

      Any help or advice would be greatly appreciated!

       

      Many Thanks,

       

      Marcus

        • 1. Re: Dynamically changing 'date' filter
          Andrew Watson

          One option is to convert your integer value to an actual date (see MAKEDATE or DATEPARSE functions) then use that as a filter showing the current month by default. A relative date filter is what I mean.

          • 2. Re: Dynamically changing 'date' filter
            Marcus Bright

            Hi Andrew,

             

            Thanks for your reply and apologies for not getting back to you sooner. 

             

            I've looked this up and whilst it seems plausible i am still a bit confused about how would i go about creating the date out of a value which only has single values representing the months.

             

            It would also need to be this type of calculation (Financial year onwards):

             

            1 = April

            2 = May

            etc.

             

            Kind Regards,

             

            Marcus

            • 3. Re: Dynamically changing 'date' filter
              Andrew Watson

              I assume you also have a year field? This is needed for makedate and dateparse. If not then you would have to manually create one to represent the year of the data. It's easiet just to use 1 as the day part of makedate, putting all dates to the first of the month.

               

              Once you have managed this and have a date it's possible to set the financial year Fiscal Dates

               

              If 1 is the month of April in your data then you may have to use DATEADD to make Jan (calendar 1) into April (fiscal 1) - the logic around this can get tricky but it's a mathematical challenge more than Tableau.