5 Replies Latest reply: Aug 7, 2018 6:11 PM by David Moore RSS

    2 date columns, count on hour

    David Moore

      I have 2 date columns and need to count using their hour for each, but in the same graph.

       

      For example:

      col

      Arrive DateLeave Date
      12/07/2018 07:0012/07/2018 08:00
      12/07/2018 08:0012/07/2018 09:00

       

      I need to count both columns using their hour.

       

      So i want to end up with:

       

      For ArriveFor Leave
      hour 7 = 1Hour 7 = 1
      hour 8 = 1Hour 8 = 1
      Hour 9 = 0Hour 9 = 1

       

      When i try to use =hour(arrivedate) in the dimensions

       

      then add two measures thus:

       

      count(hour(arrivedate)

      count(hour(LeaveDate)

       

      it doesn't work.

       

      How can i make this work??

        • Re: 2 date columns, count on hour
          Gabor Tarnoczai

          Hi David,

           

          try this:

          =interval(LeaveDate-ArriveDate','h')

           

          compact example:

          =interval('2018-01-01 13:00'-'2018-01-01 01:00','h') ->gives you 12.

           

          G.

          • Re: 2 date columns, count on hour
            Ali Ahmad

            Hi,

             

            So I would use #Timestamp() for your Date-Time field to get that interpreted and then wrap that with Hour() to get an hour column and then you can count that?

             

            Somethign like this:

             

            Temp_Data:


            LOAD * Inline [


            Number, ArriveDate, LeaveDate

            1, 12/07/2018 07:00, 12/07/2018 08:00

            2, 12/07/2018 08:00, 12/07/2018 09:00


            ];


            Data:


            NoConcateNate


            LOAD


            Number,


            TimeStamp#(ArriveDate,'MM/DD/YYYY HH:mm') As ArriveDate,

            Hour(TimeStamp#(ArriveDate,'MM/DD/YYYY HH:mm')) As ArriveHour,

            TimeStamp#(LeaveDate,'MM/DD/YYYY HH:mm') As LeaveDate,

            Hour(TimeStamp#(LeaveDate,'MM/DD/YYYY HH:mm')) As LeaveHour


            Resident Temp_Data;


            Drop Table Temp_Data;


            Capture.PNG


            Best,


            Ali A

            • Re: 2 date columns, count on hour
              David Moore

              Thanks but I'm not interested in the interval, I'm interested in counting how many dates fall in each hour of the day.so of I have 3 dates around 9:00 that would be a count of 3. If I have 2  dates around 1400, that is a count of 2 and so on...

               

              I need a count like this for each column, shown in a graph. Base on one column is easy, because I can key on the dates from that column, but when it comes to including the other column, graphed into another bar on the same graph, I'm finding very difficult.

                • Re: 2 date columns, count on hour
                  Gabor Tarnoczai

                  sorry, I was inattentive, I read it again, and understand the problem

                   

                  I think your data should arrange in other way:

                   

                  DateTime              | Type

                  2018-01-01 12:00 | Arrival

                  2018-01-01 21:00 | Leave

                   

                  and the hour(DateTime) will be the common dimension, the measure can be count(*) and add dimension Type, to get two line/bar.

                   

                  G.

                • Re: 2 date columns, count on hour
                  David Moore

                  Damn it. I should've seen that. I've un-pvioted it in SQL and it works great now. Then i used set analysis for each BAR to produce the values i was after:

                  • =count({$<adStatus={"ADM_DTM"}>}adStatus)
                  • =count({$<adStatus={"DIS_DTM"}>}adStatus)

                  Thanks