3 Replies Latest reply on Aug 13, 2018 1:33 AM by Simon Runc

    Filter but not exact match?

    Angela.Koh

      Hi friends, hope you all are having a good day

       

      I would like to check if it is possible for Tableau to set filters but not to an exact match.

      As you can see in the dashboard below; I have selected everything in Sheet 1 and only 3 instances (with the same [StartTimestamp]) were found in Sheet 2.

      I would like the filter to be a period of [Start timestamp] to -10mins of [Start timestamp] instead of exact match.

      Hope you all can shed some light! Thank you ^^

        • 1. Re: Filter but not exact match?
          Simon Runc

          hi Marcus,

           

          So this one is a little tricky, but just about possible. The key here is to create time-stamp bins (of 10 mins) so we can send the bin, and not the exact time.

           

          So first in each data source I've created the following calculations, which just truncates the date-time to 10 mins bins (a bit like when we group to Week, or Month is dates)

          [Start TimeStamp 10 min Bin]

          DATETIME(INT((FLOAT([Start Timestamp])) * (144 / 1)) / (144 / 1))

           

          I then add this into the level of detail for each of the 2 vizes. I then set up the action to just use this dimension to filter on.

           

           

          So now it's sending the 10 min bin, and not the exact datetime. By altering the 144 you can change the bin size (eg. 96 would be 15 min bins)

           

          Hope that helps and makes sense.

          • 2. Re: Filter but not exact match?
            Angela.Koh

            Simon Runc

             

            I cannot believe this is possible! Thank you so much for your help sir u are truly a guru, hope that i can reach your level in the future.

            I have 2 questions tho,

             

            1) Why is 144 = 10 mins? 96 = 15 mins? How is this calculated?

            2) I noticed that the bin is + - 10 mins... Is it possible to just have it as -10 mins instead?

             

            Looking forward to your advice 

            • 3. Re: Filter but not exact match?
              Simon Runc

              hi Marcus,

               

              So with regards this solution. It's not so much +/- 10 mins, more that it is putting Exact times into 10 min bins

               

              So all times between 9:00am and 9:10am are assigned the "bin" 9:00, then all times between 9:10am and 9:20am are assigned the bin 9:10...so this way we can send a "less exact" time through the filters. It's like when we TRUNCing a day-date to Month.

              As such I can't think of a way of send each time + 10 mins, as we don't have the same set of times in the other data source to create the same levels. I'm just going to ping Yuri Fal as he might know a way to do this (and also loves this kind of challenge )

               

              With regards the 144/96...Dates are stored in computers as integers (they are then converted to human readable format by the display). A date is the number of days from (and this depends on the software) from 01/01/1900. If you put a date into Excel, and then change the formatting display to number you'll see what I mean. This means that DateTimes are stored as decimal numbers. The integer 1 is a complete day, so 1/24 is the decimal for an hour, 1/(24*60) for a minute and so on...

               

              So the datetime now is 13/08/2018 09:15 which equals

               

              43325.3857

               

              Integer part is days from 1/1/1900

              Plus 9 * (1/24)

              Plus 15 * (1/(24*60))

               

              So what we are doing with the DATETIME(INT((FLOAT([Start Timestamp])) * (144 / 1)) / (144 / 1)) is "rounding" this decimal to 10 mins.

               

              An analogy would be Longitude/Latitude...each decimal place relates to a degree of precision. So the first decimal place of a long/lat is worth around 1,000km, so if we round our long/lat to 1 decimal places all long/lats within 1,000km will be marked on the same point. We are just doing the same with time.