1 2 Previous Next 23 Replies Latest reply on Aug 4, 2016 2:19 PM by Shinichiro Murakami

    Present events during a floating timeframe.

    Dim Pl

      Hello

       

      I 've been trying for quite some time to deal with the following problem.

      I have data including [Serial] and [datestart] and i would like to present the serial numbers which have a +-10 minute difference to a particular serial.

      I.e. when choosing a particular [serial] number show all the [serial] numbers which have been activated [datestart] within a 20 minute timeframe and present them on a graph where date is on the X-axis and time is on the Y-axis.

       

      Please use the Excel file attached.

       

      Thanks in Advance

        • 1. Re: Present events during a floating timeframe.
          Shinichiro Murakami

          Hi Dim,

           

          I hope attached helps.

           

           

           

          [date-X]

          date([Datestart])

           

          [Time-Y]

          DATETIME([Datestart]-[date-X])

           

          [Time Happened]

          {fixed : min(if [Parameter 1]=[Serial] THEN [Datestart] END)}

           

          [In / Out]

          if abs([Datestart]-[Time Happened])<=(1/24/60)*10 then "in" else "out" end

           

           

           

          Thanks,

          Shin

           

          9.2 attached.

          • 2. Re: Present events during a floating timeframe.
            Dim Pl

            Thank you so much for your super fast answer.

            Unfortunately the X-axis represents the [serial] and NOT the [date].

            If you have the time please reconsider the answer.

             

            Best Regards

            Dim

            • 3. Re: Present events during a floating timeframe.
              Dim Pl

              In order to give you a better view of the expected result I attach a sample of the points of a random [serial].

              in this particular graph I would like to add the points of those [serials] which started within +- 10 minutes from the moment the [serial] I initially choose happened.

               

              Screen Shot 2016-08-01 at 22.25.52.png

              Regards

              Dim

              • 4. Re: Present events during a floating timeframe.
                Shinichiro Murakami

                The sample did not have enough data points, but simply move Serial to Detail.

                 

                 

                And change "Time" to Hour.

                 

                Thanks,

                Shin

                • 5. Re: Present events during a floating timeframe.
                  Dim Pl

                  Thanks once again for trying to help out.

                  Here is the result from all the data:

                  Screen Shot 2016-08-02 at 01.36.45.png

                  Unfortunately it doesnt carry much information.

                  Thats why I would like to provide the whole data set for you to try out.

                  • 6. Re: Present events during a floating timeframe.
                    Shinichiro Murakami

                    Isn't it depend on which serial you choose thru parameter?

                     

                    Or I might still miss-understand your request...

                     

                     

                    Thanks,

                    Shin

                    • 7. Re: Present events during a floating timeframe.
                      Dim Pl

                      Hello Shin

                       

                      Unfortunately the suggested strings doesn't present the desired outcome.

                      Please refer to the previous answer posted by me at the AUG 1,2016 1:34.

                       

                      In order to explain further what the graph should present I will describe the real business problem:

                       

                      I got a list of customers [serial] and the time [datestart] in which they use the service.

                      I would like to create a graph so I can present client dependency.

                      In other words I would like to figure out if a particular customer can attract other customers to use the service at the same time.

                       

                      I want to choose a customer [serial] and :

                       

                      Step 1: Have a graph showing the points in time [datestart] he/she used the service (as shown in AUG 1,2016 1:34)

                       

                      Step 2: Add on this graph the points in time [datestart] of other customers [serial] who used the service within a +- 10 minute interval from the time [datestart] of the first customer.

                       

                      I hope now you get a better picture of the problem.

                      Your help is highly appreciated.

                       

                       

                      Kind Regards

                      Dim

                      • 8. Re: Present events during a floating timeframe.
                        Shinichiro Murakami

                        I still don't understand what is missing.

                        I created Time x minutes table to show micro scope.

                        You can see red dot as "selected customers start point"

                         

                        And Blue dots as plus/minus 10 minutes.

                         

                        It shows the required data I think.

                        Again, the sample does not have enough data and that doe not work very well..

                         

                         

                        Thanks,

                        Shin

                        • 9. Re: Present events during a floating timeframe.
                          Dim Pl

                          Hello Shin

                           

                          The last version of your file is towards the right direction.

                          There is only one last problem to address.

                          When data sample gets large the points marked as "out" overwhelm the slides.

                          Filtering the [in/out] calculated field in order to exclude "out" ruins the graph.

                          Any suggestions about this issue?

                           

                           

                           

                           

                          P.S If you have the time can you please explain why did you use

                           

                          1.

                          abs([Datestart]-[Time Happened])<(1/24/60)*10

                          in the  [in/out]  field

                           

                          and

                           

                          2.

                          fixed : min

                          terms in [Time happened]

                           

                          Thanks in advance

                          Dim

                          • 10. Re: Present events during a floating timeframe.
                            Dim Pl

                            Update*

                             

                            Filtering the [datestart] also ruins the graph.

                            It automatically excludes the "in" and "Selected Customer Starts" observations.

                            • 11. Re: Present events during a floating timeframe.
                              Shinichiro Murakami

                              One idea to show appropriate VIZ Range is to add one more category between "IN" and "OUT" as "Range"

                               

                               

                              [Range]  // in this example, +- 12 hours as 720 mins //

                              if [Datestart]=[Time Happened] then "Selected Customer Starts"

                              elseif abs([Datestart]-[Time Happened])<(1/24/60)*10 then "in"

                              elseif abs([Datestart]-[Time Happened])<(1/24/60)*720 then "Range"

                              else "out" end

                               

                               

                              The tableau handle "day" of date as "1" just as same as excel and

                              1/24/60 * 10 = 1 day / 24 hours / 60 minutes * 10 minutes

                               

                              {fixed:min(***)}'s  "min" does not have special meaning, because that filed only has one value and Min/Ave/Sum/Max whatever you use brings same results.

                              If there are situation this field have multiple values, max brings the latest date, and Min brings the oldest date.

                               

                              Thanks,

                              Shin

                              • 12. Re: Present events during a floating timeframe.
                                Dim Pl

                                Hello again

                                 

                                Here is a workbook of my initial attempt to tackle this problem.

                                I created calculated fields in order to turn time into seconds so please

                                pay attention to the [Time Diff 10min] and [Friend OR Other Alternative] fields.

                                 

                                 

                                Selected Customer worksheet presents the points in time where the customer of

                                reference used the service during a particular time frame.

                                 

                                Related Customers worksheet was supposed to show all other customers who

                                used the service during a +- 10 minute timeframe from the points of Selected Customer.

                                 

                                Unfortunately there is some mistake in my strings and the program fails to compare each and every

                                date with the selected customers date and present the points.

                                Please in case you have the time take a look at my workbook and help me improve it.

                                 

                                Kind Regards

                                Dim

                                • 13. Re: Present events during a floating timeframe.
                                  Shinichiro Murakami

                                  It seems like that you took it too much complicated.

                                   

                                  [Filter]

                                  if

                                      [Datestart]=[Customer Datestart]

                                      then "Selected"

                                  elseif

                                      abs([Datestart]-[Customer Datestart])<1/(24*60)*10

                                      then "in +- 10 minutes"

                                  elseif abs([Datestart]-[Customer Datestart])<1/(24*60)*60

                                      then "in +- 60 minutes"

                                  else "OUT" end

                                   

                                   

                                   

                                   

                                  Thanks,

                                  Shin

                                  • 14. Re: Present events during a floating timeframe.
                                    Dim Pl

                                    Hej Shin

                                     

                                    Thanks for your noteworthy dedication to my problem.

                                    Unfortunately the suggested solution doesn't work properly.

                                    If you look at the data the selected customer didn't use the service during the 1/1/2015 and the [filter] excludes all other dates he actually did use the service.

                                    Further optimisation is needed so i will also attach the excel data file.

                                     

                                    Thanks again

                                    Dim

                                    1 2 Previous Next