2 Replies Latest reply on Feb 28, 2016 10:00 PM by Bora Beran

    Using 30 day segments instead of 'month'

    trevor.badorrek.0

      I'm trying to use a date dimension on my columns shelf, as a discrete dimension. However, instead of using MONTH, I'd like to look at 30 day segments. So my columns would be broken up by 0-30 last days, 31-60 last days, 61-90 last days, etc.

       

      Is this possible?

        • 1. Re: Using 30 day segments instead of 'month'
          Shinichiro Murakami

          Trevor,

           

          You can do that with below calculated field for example.

           

          [Date Group]

          if today()-[Date] <=30 then "in 30 days"

          elseif today()-[Date] <=60 then "30-60 days"

          elseif today()-[Date] <=90 then "60-90 days"

          else "90 days or more"

          END

           

          Today() is dynamic, so the results will differ according to the date in this case.

           

          Thanks,

          Shin

           

          9.0 attached

          3 of 3 people found this helpful
          • 2. Re: Using 30 day segments instead of 'month'
            Bora Beran

            The are more scalable ways of doing this.

             

            The easiest way is to do the following,

             

            Assuming you're trying to segment each row based on some start date where start date is the first date in your table

             

            {MIN([Your Date Field])} is the first date in your entire table (this is an LOD expression and requires using Tableau 9.0 or higher)

             

            You can write

             

            DATEDIFF('day',{MIN([Your Date Field])}, [Your Date Field])

             

            to get the number of days between the first date and current row. Save this as a field, right click on it, create > Bin. Select 30 from the options.

             

            You can use the bin as a dimension in your view.

             

             

             

            If you want to label them but don't want to do it manually since there is a large number of categories. Starting with the same equation

             

            DATEDIFF('day',{MIN([Your Date Field])}, [Your Date Field])

             

            which will give you the difference between the first ever date and the current row

             

            You can do the following to bin it manually

             

            INT(DATEDIFF('day',{MIN([Your Date Field])}, [Your Date Field]))/30

             

            what this will do is if the difference is 29, it will give you 29/30 then round down so you get 0.

             

            If difference is 32 it will do 32/30 then round down so you get 1.

             

            If you use this as a dimension in your view it will nicely break thing into 30 day buckets but labels will read 0, 1 , 2 etc. which you may not want.

             

            Let's say you made

             

            INT(DATEDIFF('day',{MIN([Your Date Field])}, [Your Date Field]))/30

             

            a calculated field named Calculation1

             

            then you can write

             

            STR(1+ Calculation1*30)+'-'+STR((Calculation1+1)*30) + 'days'

             

            when calculation1 is 0 this will give you '1-30 days' if it is 1 it will give you '31-60 days' etc.

            2 of 2 people found this helpful