10 Replies Latest reply on Jan 31, 2019 12:11 PM by Pauline Tisseyre

    Filter last 10 days in multiple years

    Pauline Tisseyre

      Hi!

       

      I have set a grid that displays the Total number of Registrations per day for 2017, 2018 and 2019.

       

      1)But I would like to set a filter that shows only the last 10 days (based on the last full day which is yesterday). Any thoughts on how to do it?

      Currently I have a filter that allows me to show daily registration up to yesterday (Current YTD), but I would like to find a way to see the daily registration for the last 10 days.

       

      2)I am using a date dimension (Transaction Day) that is set as m/dd but Tableau recognizes it as m/dd/1900... Do you know a way to remove the year in a date format?

       

      Thank you!
      Pauline

        • 1. Re: Filter last 10 days in multiple years
          Hari Ankem

          Is this the expected output?

          1.png

           

          If yes, you can see that I have applied a filter as shown above, and added all the other filters to context by right-clicking on them.

           

          Hope this helps.

          • 2. Re: Filter last 10 days in multiple years
            Alyn Still

            I'm not sure how the data is being presented to tableau, but it looks like it's only ending up with part of the date in the transaction day field, with the year coming in a separate field 'Year'.

            That makes it fairly simple to piece them back together to get the full date in one field - which would make things a little easier to use. I created a new calculated field 'Transaction Day - corrected' using the calculation:

            MakeDate(int([Year]) ,month([Transaction Day]) ,day([Transaction Day]) )

            That takes the day and month from your existing transaction day and combines them with 'Year'.

             

            If you only want the last ten days of this year, you could filter on this directly using relative date or a top(n) filter - but that would include the current day could give partial data depending on how things get updated.

             

            For more complicated date logic I usually create a calculation to work out the "Relative Day" - with yesterday being -1, today being 0, and tomorrow being 1. On your data that could be done with the calculation:

            datediff('day',today(),[Transaction Day - corrected])

            If you want to filter to show the last ten days plus the equivalent period from previous years - then the calculation would be a little more complicated:

            datediff('day'

                        ,today()

                        ,dateadd('year'

                                    ,datediff('year',[Transaction Day - corrected],today())

                                    ,[Transaction Day - corrected]

                                )

                    )

            I've attached a copy of your workbook with examples of these two methods.

            • 3. Re: Filter last 10 days in multiple years
              Pauline Tisseyre

              Hi Hari,

               

              Thanks for your answer. This helps but unfortunately it doesn't give me the information I am looking for. I would like the cumulative number of Registrations since the beginning of the year. Right now the numbers show Registrations within the last 10 days only. We don't have the Registrations that occurred before 01/19 taken into account. But I would like them to. Any thoughts to have them included?

               

              Thanks!
              Pauline

              • 4. Re: Filter last 10 days in multiple years
                Pauline Tisseyre

                Hi Alyn,

                 

                Thanks for your feedback! But i have the same answer as for Hari. I would like the cumulative number of Registrations since the beginning of the year. Right now the numbers show Registrations only within the last 10 days only. In other words, we don't have the Registrations that occurred before 01/19 taken into account. But I would like them to. Any thoughts to have them included?

                 

                Thank you!

                Pauline

                • 5. Re: Filter last 10 days in multiple years
                  Hari Ankem

                  OK. Is this what you are expecting?

                  1.png

                  To achieve the above, I have created 2 calculated fields as shown below:

                   

                  Temp Date Computed: MAKEDATE(YEAR(TODAY()),MONTH([Transaction Day]),DAY([Transaction Day]))

                   

                  Show/Hide:

                  IF [Temp Date Computed]<=TODAY() AND [Temp Date Computed]>TODAY()-10 THEN

                      "Show"

                  ELSE

                      "Hide"

                  END

                   

                  I have placed the Show/Hide value as given below, then hide the "Hide" values, and suppress the "Show" header.

                  1.png

                   

                  Hope this helps.

                  1 of 1 people found this helpful
                  • 6. Re: Filter last 10 days in multiple years
                    Pauline Tisseyre

                    This is really helpful Hari, thanks!

                     

                    How did you hide the "Hide" values?

                     

                    Thanks!
                    Pauline

                    • 7. Re: Filter last 10 days in multiple years
                      Hari Ankem

                      You can right-click on the "Show/Hide" field and should see an option to view the hidden data. Then you can display the header too. Once, you see the header, you can hide it by right-clicking on it.

                      • 8. Re: Filter last 10 days in multiple years
                        Pauline Tisseyre

                        Hi Hari,

                         

                        Sorry but I'm not able to replicate what you did. When I right-click on "Show/Hide" field, I don't see any options to "Hide".

                         

                        Highlighted below is what I would like to hide (and not filter as it will remove values that I need to keep in the  calculation).

                         

                        Thanks!
                        Pauline

                        • 9. Re: Filter last 10 days in multiple years
                          Hari Ankem

                          Sorry, I didn't state it correctly. Please right-click on the "Hide" text you see above and you should see the hide option.

                          1.png

                          • 10. Re: Filter last 10 days in multiple years
                            Pauline Tisseyre

                            Great! Thank you Hari!