8 Replies Latest reply on Sep 7, 2015 1:17 AM by Simon Runc

    Dynamic Time Buckets

    Simon Lai

      Hello,

       

      I have (frustratingly) tried creating dynamic time buckets that hook up to a parameter with 3 options: Last 3 Months, Last 6 Months, Last 12 Months. I have a date field that has either 0, 1 or more dates per month and being read from an excel datasource where each date input is done using the =date formula ([End Date (=Date)] is this field). I currently have a monthly time bucket parameter that allows the user to select any given month using this calculated field...

       

      If [End Date (=Date)]>=DATE("12/01/2014") AND [End Date (=Date)]<=DATE("12/31/2014")  then "December 2014"

      Elseif[End Date (=Date)]>=DATE("1/01/2015") AND [End Date (=Date)]<=DATE("1/31/2015")  then "January 2015"

      Elseif[End Date (=Date)]>=DATE("2/01/2015") AND [End Date (=Date)]<=DATE("2/28/2015")  then "February 2015"

      Elseif[End Date (=Date)]>=DATE("3/01/2015") AND [End Date (=Date)]<=DATE("3/31/2015")  then "March 2015"

      Elseif[End Date (=Date)]>=DATE("4/01/2015") AND [End Date (=Date)]<=DATE("4/31/2015")  then "April 2015"

      Elseif[End Date (=Date)]>=DATE("5/01/2015") AND [End Date (=Date)]<=DATE("5/31/2015")  then "May 2015"

      Elseif[End Date (=Date)]>=DATE("6/01/2015") AND [End Date (=Date)]<=DATE("6/31/2015")  then "June 2015"

      Elseif [End Date (=Date)]>=DATE("7/01/2015") AND [End Date (=Date)]<=DATE("7/31/2015")  then "July 2015"

      Elseif [End Date (=Date)]>= DATE("8/01/2015") AND [End Date (=Date)]<=DATE("8/31/2015")  then "August 2015"

      Elseif [End Date (=Date)]>=DATE("9/01/2015") AND [End Date (=Date)]<=DATE("9/30/2015")  then "September 2015"

      Elseif [End Date (=Date)]>=DATE("10/01/2015") AND [End Date (=Date)]<=DATE("10/31/2015")  then "October 2015"

      Elseif [End Date (=Date)]>=DATE("10/01/2015") AND [End Date (=Date)]<=DATE("10/31/2015")  then "October 2015"

      Elseif [End Date (=Date)]>=DATE("11/01/2015") AND [End Date (=Date)]<=DATE("11/30/2015")  then "November 2015"

      Elseif [End Date (=Date)]>=DATE("12/01/2015") AND [End Date (=Date)]<=DATE("12/31/2015")  then "December 2015"

      END

       

      My first thought to add in the Last 3,6,12 Months options was to add to this list. I attempted this by adding these lines (an obviously moving the "END" to the bottom)

       

      Elseif [End Date (=Date)]>=dateadd('month',-12,(today())) AND [End Date (=Date)]<=today() then "Last 12 Months"

      Elseif [End Date (=Date)]>=dateadd('month',-6,(today())) AND [End Date (=Date)]<=today() then "Last 6 Months"

      Elseif [End Date (=Date)]>=dateadd('month',-3,(today())) AND [End Date (=Date)]<=today() then "Last 3 Months"

      END

       

      Although the calculated field is valid, when I select the option from the drop down list from the parameter it returns no data.

       

      I then tried another method by creating a separate calculated field for just these three time buckets

       

      If [End Date (=Date)]>=dateadd('month',-12,(today())) AND [End Date (=Date)]<=today() then "Last 12 Months"

      Elseif [End Date (=Date)]>=dateadd('month',-6,(today())) AND [End Date (=Date)]<=today() then "Last 6 Months"

      Elseif [End Date (=Date)]>=dateadd('month',-3,(today())) AND [End Date (=Date)]<=today() then "Last 3 Months"

      END

       

      Using this method the Last 12 Months options works but the other options return nothing.

       

      I then created three separate calculated fields each one with just one line form the above code.

       

      For example, for the Last 12 Months:

      If [End Date (=Date)]>=dateadd('month',-12,(today())) AND [End Date (=Date)]<=today() then "Last 12 Months"

      END


      I then hooked each calculated field up into 3 different parameters and of course they all 3 work.


      I started thinking that the reason having them all in one calculated field doesn't work is because they are non-mutually exclusive sets and thus the Elseif formula breaks down.


      Can someone please help me resolve this problem. I want one parameter that has 3 options (Last 3,6,12 Months).


      I have attached a packaged workbook.

        • 1. Re: Dynamic Time Buckets
          Simon Runc

          hi Simon,

           

          Can I just double check my understanding of what you were after, as the text question, and the title seem different!

           

          My understanding of buckets (or Bins) is to group things together. Such as you'd do for a Histogram, but from the question text, it sounds like you want to create dynamic filters (i.e. Show last 3 Months, 6 Months, 12 Months..) and to display the information by Month, based on the above filtering.

           

          If this is what you need, this is fairly easy in Tableau as for a date it has an understanding of the 'hierarchy' of dates, so can be displayed by Day, Month, Quarter..

           

          Let me know and I'll send you over a solution

          • 2. Re: Dynamic Time Buckets
            Dana Withers

            Hi Simon,

             

            There are several options to do this:

            I think the easiest would be to use the date filter to select last 12, 6 or 3 months. It is easy to amend.

            You can also set up a filter by Month (using a custom date of Month and setting it to discrete)

             

            If you want to go the painful way and calculate it so you only have one dropdown, I think you'll need to reorganise the formula. I've made a start...

            if [Select Time Bucket] = "(All)" then 'Show'

            elseif [Select Time Bucket] = "Last 12 Months" and [End Date (=Date)]>=dateadd('month',-12,(today())) AND [End Date (=Date)]<=today()  then 'Show'

            elseif [Select Time Bucket] = "Last 6 Months" and [End Date (=Date)]>=dateadd('month',-6,(today())) AND [End Date (=Date)]<=today()  then 'Show'

            elseif [Select Time Bucket] = "Last 3 Months" and [End Date (=Date)]>=dateadd('month',-3,(today())) AND [End Date (=Date)]<=today()  then 'Show'

            elseif [Select Time Bucket] = "December 2014" and [End Date (=Date)]>=DATE("12/01/2014") AND [End Date (=Date)]<=DATE("12/31/2014")  then 'Show'

            else 'Hide'

            end

            Then set this calculation on your filter and choose "Show" only.

             

            This may be a little hard to test with your dataset as when you select all... only  a few months in 2015 are available.

             

            Hope that helps,

             

            Dana

            • 3. Re: Dynamic Time Buckets
              Simon Lai

              Hi Simon,

               

              Thanks for the response!

               

              You are correct, I do want dynamic date filters based of the last 3, 6 and 12 Months (my apologies for the confusion...I refer to each one of those as buckets which I know is something altogether different).

               

              Your help is greatly appreciated.

              • 4. Re: Dynamic Time Buckets
                Simon Lai

                Hi Dana,

                 

                That worked perfectly. Thank you very much!

                 

                Armon

                • 5. Re: Dynamic Time Buckets
                  Simon Runc

                  ...well look like you have a solution.

                   

                  I'd have suggested something very similar....

                   

                  I'd have taken advantage of the fact that a parameter can be one type of data, but display another. If you look at the below it's actually passing any formula, i use it in, the numbers 3,6,12, but displays to the user 'Last 3 Months', 'Last 6 Months'...

                   

                  I can then use this in a similar formula to Dana's

                   

                  IF [Show Last N Months] = 1 THEN  'Show'

                  ELSEIF   [End Date (=Date)]>=dateadd('month',-[Show Last N Months],(today())) AND [End Date (=Date)]<=today() THEN 'Show'

                  END

                   

                  Parameter Set Up 2.PNG

                   

                  Nice thing here is that if you want to extend the number of options to 'last month', 'Last 9 months'...etc. you only need to update the parameter, and not the formula.

                   

                  Always lots of ways to do things in Tableau!!

                  1 of 1 people found this helpful
                  • 6. Re: Dynamic Time Buckets
                    Simon Lai

                    Thanks Simon, I will keep this solution in mind! I'm sure I will need something like this on my next project!


                    Armon

                    • 7. Re: Dynamic Time Buckets
                      Dana Withers

                      Oh I like that option! And I think numeric parameters are quicker than string ones aren't they?


                      Is there an easy way that your solution can be combined with the choice of individual months in the same parameter?

                      I usually put a date range selector in, but that is quite more fiddly than a simple picklist.


                      Thanks!


                      Dana

                      • 8. Re: Dynamic Time Buckets
                        Simon Runc

                        hi Dana,

                         

                        Yes there are a few advantages, and yes (and you only really notice this on larger data sets) passing integers is more efficient than passing strings. The other thing I've also found is maintenance....say you set up a parameter so the user can select the date-slice to look over...(say) Calendar Day, Calendar Week, Financial Week...by using strings when the client comes back and say "I'd like Finance Week, to be called 'Retailer Week'"..., it a question of finding all the calculations and changing the IF [Parameter] = 'Financial Week'..and change to IF [Parameter] = 'Retailer Week'...using integers we just change the 'alias' in the parameter. The slight downside is that when writing your formula accessing the parameter you have to remember which numbers refer to which things!!


                        Yes you can...If you use the DATEPART function it returns the Integer of the specified time-part. For example DATEPART('month' #22/03/2015#) = 3 (and this is the same for day. week, quarter...etc.). In this way you can use integers (which also allow you to do > and <)