3 Replies Latest reply on Oct 11, 2017 10:10 PM by Vijeth Krishna

    Creating a series of dates

    Jatin Kakani

      Hi folks,

       

      I want to create a field with a series of dates for last 6 months. Is there any way I can do that ?

       

      Thanks.

      Regards,

      Jatin

        • 1. Re: Creating a series of dates
          Bora Beran

          You can create a series of any length starting with a 2 row data table that looks like this.

           

          ID

          1

          2

           

          By creating a calculated field such as

           

          IF [ID]=1 then DATEADD('month',-6,TODAY()) else TODAY() END

           

          And dragging into your view and changing it to the granularity you month e.g. MONTH(Date)

           

          Then from the context menu selecting show missing values.

           

          At this point if you add an INDEX() calculation on the opposing axis you will see a number of every month in that 6 month period. If you drill down to date, then you will see a number for every day within that 6 month period etc.

           

          You can use these densified rows with table calcs hence index() will work but SUM() for example won't honor the extra rows you're creating on the fly.

          1 of 1 people found this helpful
          • 2. Re: Creating a series of dates
            Sreekanth Kasaraneni

            Hi Jatin,

             

            Can you please explain what you are trying to achieve, sample workbook with some example might help.

             

            Thanks,

            Sreekanth.

            • 3. Re: Creating a series of dates
              Vijeth Krishna

              He wants to create a series of dates without actually connecting to a data source that has the last 180 dates.