1 2 Previous Next 24 Replies Latest reply on Apr 17, 2017 4:32 AM by Rishabh Dhingra

    Calendar Date as Field Within a Table

    Erin Gehn

      Hello~

      I need to compare some data based on a calendar date. Calendar date is not a field within my joined tables.

      For past reports, I have just used a parameter to identify calendar date. However, now I must use it within the actual data comparison (ie. chart, illustration, graph) so I cannot use a parameter (or can I?).

       

      The desired view is a list of the calendar dates from January of this year until now. To the right of that date will be a calculated amount based on that date.

       

      How can I accomplish this?

      Any feedback could be helpful!

        • 1. Re: Calendar Date as Field Within a Table
          Patrick Cloutier

          Hi Erin,

           

          If you have a field that represents a date (a string of some sort), you can create a date from that field.

          How do you decide what date you would attribute to what record?

           

          Here is an example of a way to create a date via a string:

          e.g.:  DATEPARSE('dd/MM/yyyy', '01/01/1900') converts the string 01/01/1900 to a date.

           

          Hope that helps.

           

          -Pat-

          • 2. Re: Calendar Date as Field Within a Table
            Erin Gehn

             

            How do you decide what date you would attribute to what record?

             

             

             

             

            I have a set of date ranges that contain corresponding data. The calendar date should be constant (in that it should increase one day every row regardless of the data). The date ranges will then also be in chronological order and the calendar date will fall directly between the dates of the range.

             

            I'm not sure if this is very clear, but I think you have helped me see a route. I shall check back soon!

            • 3. Re: Calendar Date as Field Within a Table
              Erin Gehn

              I was incorrect in that path.

               

              Given my new clarity on the subject, please allow me to amend::

              I have a single date field, Occurrence Date. Given a calendar day (which still increases at a day increment per row), I must add and subtract 2 to find a calendar date range. This range collects data from the Occurrence Dates that lie within that range.

               

              I need to create/locate a calendar date range.

              • 4. Re: Calendar Date as Field Within a Table
                Erin Gehn

                I have set up the data to display:

                Calendar Day (a single one created via parameter) / Begin Date / End Date  / [Respective Data]

                 

                This works for what I need, but it has been requested further (and as I mentioned above) that I need a list of calendar days with the Begin Date/End Date/[Data] following for each day.

                 

                Is there a way to do this?

                This is what I envision dynamic parameters helping with, but perhaps there is another way?

                I would super appreciate any feedback

                Thank you in advance!

                • 5. Re: Calendar Date as Field Within a Table
                  Patrick Cloutier

                  I might not understand fully, so bear with me.

                   

                  Do you already have a before and after day created?

                  If not, you can create them with this formula:

                  DATEADD('day', 2, [Date Dimension])

                  where [Date Dimension] is whatever your date is.

                  You can parameterize this as well if you want the people to choose but it also depends on what you want to do with it.

                  You can also add negatives to go backwards in time (for the begin date).

                  But as I said I might not be understanding fully what you are trying to accomplish.

                  • 6. Re: Calendar Date as Field Within a Table
                    Erin Gehn

                    I have all those fields identified and created now.

                     

                    I have all the fields I need ( I think). The only thing I need further is taking the parameter value of Calendar Day ((that displays a single row)) and creating rows of each Calendar Day- where each row adds a single day to the Calendar Day parameter I now have.

                     

                    Does this make more sense?

                    • 7. Re: Calendar Date as Field Within a Table
                      Patrick Cloutier

                      Just to make sure that I am on the same page:

                       

                      You have:

                      [Date] / [Begin Date] / [End Date] / [Respective data]

                       

                      Date is controlled via a parameter.  I am guessing you are filtering by a specific day and you want it to be a range?

                      Because if that is the case, then you can filter via a range (controlled by two parameters if you wish).

                      If you want to display:
                      [Date A -1] / [Begin Date-1] / [End Date-1] / [Data for Date A]

                      [Date A] / [Begin Date] / [End Date] / [Data for Date A]

                      [Date A +1] / [Begin Date + 1] / [End Date + 1] / [Data for Date A]

                      (i.e.: you want to duplicate the rows) then this would get complicated (and I'm not convinced that you can accomplish this with tableau)

                       

                      That is why I am hoping you only want to display a range of values...

                      But please do correct me if I am misunderstanding.

                      • 8. Re: Calendar Date as Field Within a Table
                        Erin Gehn

                        I am content with only displaying a range of values. Currently, I'm just isolating to year 2015.

                         

                        I have:

                        [Date] / [Begin Date] / [End Date] / [Respective data]

                         

                        I need:

                        [Date] / [Begin Date] / [End Date] / [Respective data]

                        [Date+1] / [Begin Date+2] / [End Date+2] / [Respective data]

                        [Date+2] / [Begin Date+4] / [End Date+4] / [Respective data]

                        [Date+3] / [Begin Date+6] / [End Date+6] / [Respective data]

                         

                        Begin Date and End Date are already set up to add 2 per [Date]. So they require no adjusting.

                         

                        Just [Date].

                        I do have the first date that was determined via parameter. As depicted above.

                        • 9. Re: Calendar Date as Field Within a Table
                          Patrick Cloutier

                          Sorry about the confusion.

                          Hopefully this helps.

                           

                          For the calculated field you should create for the range, do the following calculation:

                           

                          if [Order Date]>[DateParam] and [Order Date] < DATEADD('day', 4, [DateParam])

                          then [Order Date]

                          else null

                          end

                           

                          (I apologize for the naming, I had to test it on the Tableau samples)

                          Where [Order Date] is your date field, [DateParam] is your date parameter.

                          4 is the number of days ahead you want to show.  You can switch this to an integer parameter to give the user even more control if you wish.

                          When you drag this to filters, choose range and accept the default (it should pretty much cover all the time).

                          This should limit the data to what you are wanting (just make sure to get rid of all other filters that involve date.

                          Similarly you can make it start before the date.

                           

                          Hope that this was what you were looking for.

                           

                          -Pat-

                          • 10. Re: Calendar Date as Field Within a Table
                            Erin Gehn

                            Looking into it!

                            I'll check back soon

                            • 11. Re: Calendar Date as Field Within a Table
                              Erin Gehn

                              The code you suggested results in no view.

                               

                              Can you help me understand your code and why you chose what you did?

                               

                              • You're saying that [Date] (which by definition is the [Date Parameter]) is greater than the [Date Parameter]?
                              • If this is to display multiple rows, from one row to the next is only an increase of 1...not 4. Where does 4 come from?
                              • If [Date] is less than 4 days less of the [Date Parameter], how does that contribute exactly? What is that accomplishing?
                              • 12. Re: Calendar Date as Field Within a Table
                                Patrick Cloutier

                                I had assumed you had an underlying date field that you are filtering by use of a date parameter.

                                 

                                With no filters you should see a series of dates/ begin dates/ end dates/ respective data

                                If this is not correct, then we are not on the same page.

                                 

                                Essentially what the code does is check your dates, filter down your dates by whatever parameter you have chosen and lets 4 days pass.  (If you have entries every second, it would let all those entries pass.  Think of it as a window.  (The assumption is that all your data is incremented by day)

                                 

                                If you are purely working with date as a parameter, how do you link this parameter to your data?  Are you comparing your parameter with a date field?

                                 

                                Assuming that occurrence date is your date field, you should have that as the [Order Date] and your date parameter (whatever you have called it) as [DateParam]

                                Are my assumption correct?

                                • 13. Re: Calendar Date as Field Within a Table
                                  Erin Gehn

                                  See attached workbook headers and definitions for a clear and direct view of what I have.

                                  Thank you!

                                  • 14. Re: Calendar Date as Field Within a Table
                                    Erin Gehn

                                    Hmm...

                                    I have Occurrence Date that is underlying. But even with plugging Occurrence Date into your calculated field, I get no view.

                                     

                                    My data is not incremented by day. That is the target.

                                    1 2 Previous Next