5 Replies Latest reply on Aug 11, 2017 1:18 PM by Justin Larson

    Date Range List

    Jessica Bartlett

      I have Parameters Date and Number_of_Months and a calculated Dimension that determines a second date with that information (either in the future or in the past depending on the sign of the Number_of_Month entered). I have then created Dimensions that determine a Beginning_Date and an End_Date by doing a comparison of the two dates then then setting the Beginning_Date to be the first of the month for the earlier date and the end of the month of the later date as End_Date.

       

      I need to be able to create a list of months between these two dates for a x-axis on a line chart. Is this possible?

        • 1. Re: Date Range List
          Justin Larson

          There is a feature deep in Tableau referred to as domain padding, which has the capability to do what you are describing. How to actually perform meaningful functions with it, however, is a complicated, nuanced, and context-sensitive subject, and we'd need a lot more details to be able to be of much help on how it may be leveraged.

           

          That said, depending on your datasource, it may be conducive to instead, pass your parameters to a datasource, and have the datasource create the dates and pass back as a dataset, which you can then join or blend to your existing data. Again, more details about what you are trying to accomplish, what your data source is, and how your data looks, all would be needed to get into the hows and whys.

          • 2. Re: Date Range List
            Deepak Rai

            yes it is possible. See this thread. See the Great Work by kettan here.

             

            Calculation depending on a Start Date and a End Date and represent it on a time axis

            1 of 1 people found this helpful
            • 3. Re: Date Range List
              Jessica Bartlett

              I thought I included this. Sorry.

              • 4. Re: Date Range List
                Jessica Bartlett

                What I'm doing is difficult to explain, that's why my question is vague.

                 

                i have a data set of loans with open dates and close dates, and a status field that indicates if the loan is open or closed. What i'm needing to do is create a graph over a given time that indicates the current number of open loans. My parameters allow for the dates to be entered and altered. However, if I truncate my dates I have available, I only look at loans that were opened after my Beginning_date. I need to be able to have a hard close date, but a soft open date of some kind so I only display the data I need, but have an accumulation of all loans in an open status from the beginning of time of my full data set.

                 

                I have an Is_Open counter in place that I can use to find the number of open loans for a fixed point in time, but I need an independent date set that will not effect the beginning date of my data.

                IF [Status] = 'Open'

                OR [Status] = 'Closed'  and [Status Date] > [End Date] THEN 1

                ELSE 0 END

                 

                 

                So the real issue is, I need to display dates different than the dates I am pulling in for my data displayed in a graph. I show June, July, August, but the calculation is (-infinity, June], (-infinity, July], (-infinity, August]

                 

                i hope that makes sense.

                • 5. Re: Date Range List
                  Justin Larson

                  This is a common scenario. Short story is that the simplest way to accomplish it is to bring in or generate a calendar table containing all the dates you want on your axis, then join that to your datset where calendar.date is between data.startdate and data.enddate.

                   

                  The result does explode the size of your data, because each single record will be duplicated to for every date it was active, but that is the best way to do it when you've got a date range on each record. Then it's a pretty simple aggregation.

                   

                  Other alternatives do various logic with window functions and or LOD expressions, but most of them fall into failure under certain conditions, and are exponentially more complex to attempt.

                   

                  Search for "active customers" or "running count distinct" in the forums, and you will see all kinds of people basically trying to do the same analysis, and all the different ways people approach it.

                   

                  What your data source in your real workbook? Generating a dynamic calendar based on those parameters may be pretty easy, depending on the data source.