9 Replies Latest reply on Jan 7, 2017 2:26 AM by valerio.iacobini

    dates range

    valerio.iacobini

      Hi all! And happy new year.

      Maybe is simple, but I didn't success in it nor found an answer in the forum.

      I have a table with two date field, start and end, defining a range.

      In particular when the end field is null it means that the activity it's still in progress.

      I'd like to realize a date filter for my views...

      No problem with my starting point: I defined the filter as "starting date" and it seems to work like a charm...

      But how to evaluate the null values for the end field?

      Is there any way to establish a maximum value as "null = not ended"?

      Thanks all.

      V.

        • 1. Re: dates range
          Gene Denny

          By converting the End Date to a string, you could create a calculated field as:

           

          IF ISNULL(STR([End Date])) THEN 'On-going'

          ELSE STR([End Date])

          END

           

          Which would give you a rendering such as

           

           

          The down-side is that you lose the ability to identify the End Date Calculated as a date field.  I'm with you in that the ability to use the GETDATE() function would be REALLY helpful.

           

          Someone else may have a better option.

          • 2. Re: dates range
            valerio.iacobini

            Ok... you put me on the right track! Thanks!

            In my scenario the right thing is something like assign today to null fields.

            This way I'll always get my end limit on today (= on-going), giving the appropriate filter limit also.

            I'm trying that one:

            IF ISNULL([End Date]) THEN Today()

            ELSE STR([End Date])

            END

            Seems to work like a charm.

            I'm making some experiments, but I think the question is solved.

            Thanks!

            V.

            • 3. Re: dates range
              valerio.iacobini

              Seems to work...

              but the end date is selectable only on the calendar and not by using the draggable handle... why?

              • 4. Re: dates range
                Gene Denny

                I can't tell you without opening it in Tableau.  Can you attach a packaged workbook?

                • 5. Re: dates range
                  valerio.iacobini

                  Not the whole dashboard, but you can see my filters inside that...

                  Maybe I don't know how filters work in tableau... in fact in the view alone I can use the draggable handle.

                  The problem is when I put it in the dashboard... any idea? Maybe some view that doesn't match my filter's data?

                  The strange is that I can use the calendar, and that it seems to work.

                  manythanks!

                  • 6. Re: dates range
                    Gene Denny

                    I think you just need to change your filter type:

                     

                    • 7. Re: dates range
                      valerio.iacobini

                      Hi Gene and thanks for the attention.

                      Sorry: my view is in italian.

                      First handle (data ingresso) is my start point and it works well when in a dashboard.

                      Second handle is my end date (data uscita) and it only works in the view alone.

                      When mounted on the same dashboard, its draggable fuction is unactive and the only way to set an end date is selecting it in a calendar.

                      I tried to see the settings... but both the filters seem to have the same.

                      Any idea?

                      Maybe the problem is related to the calculated field?

                      In fact the only difference between the two filters is that the start point is a normal field when my end point is a calculated one.

                      Thanks.

                      V.

                      • 8. Re: dates range
                        Gene Denny

                        Yeah, I don't know that I have a solution for you on that.  I'm sure it's due to the calculated field which redefines the field from a date field to a string.

                        • 9. Re: dates range
                          valerio.iacobini

                          Something very strange.

                          I verified that the calculation on null is only valid on extract datasource.

                          The problem with the filter is in realty related to the calculation results.

                          When I'm on a live connection values persist as null.

                          On extracted datas calculation is valid and set the values on today...

                          Should I consider that also for other calculated fields?

                          Thanks.

                          v.