3 Replies Latest reply on Dec 7, 2016 9:25 AM by Muthappan Alagappan

    calculation based on time

    Muthappan Alagappan

      Hi Experts,

       

      i have a order data for last 10 days, which includes creation date and creation time(HHMMSS), i would like to get a crosstab data for last 10 days of order counts same time for last 10 days as of now.

       

      For example if the orders for each day count will be more, but i want to know as of this time what was the order count for last 10 days. so i can compare whether we are meeting todays order count.

       

      i tried using now function but i couldnt find what is the current time so i can compare creation time column and get the order counts for last 10 days.

       

      Any help will be appreciated.

       

      Thanks

      Muthappan

        • 1. Re: calculation based on time
          Tom W

          NOW() returns the current date and time, so not sure what you're struggling with there in terms of being able to find the time.

           

          It sounds to me like you first need to create a calculated field to combine the creation date and time. Something like this will work assuming your CreationDate is a date and the CreationTime is also a datetime field; datetime(str([Creation Date]) + " " + right(str([Creation Time]),8))

           

          Once you have this 'combinedtime' you can create another calculated field like;

          datediff('hour',combinedtime,now()) <= (10*24)

           

          This will evaluate to true if the difference in hours between the combined time and now() is less than or equal to 240 hours (10 days * 24 hours)

           

          Drag this field onto your filter shelf, set it to true, then drag your creation time onto columns and you have your table.

          • 2. Re: calculation based on time
            Norbert Maijoor

            Hi Muthappan,

             

            Find my approach as reference below and stored in attached workbook version 9.3

             

            Last 10 days: if DATEDIFF('day',[date],today())>=0 and DATEDIFF('day',[date],today())<=9 then [date] END

             

            • 3. Re: calculation based on time
              Muthappan Alagappan

              Thanks Tom, i think i have not explained correctly.

               

              so each day 100 orders are created, as of 9AM we get 30 orders. so i want to know as of 9AM what was created today vs last 10 days.

               

              i am able to figure out another option. i created new field called currenttime

               

              INT(STR(DATEPART('hour',now()))+ STR(DATEPART('minute',NOW()))+ STR(DATEPART('second',now())))

               

              and then i converted my created time string field into HHMMSS_createdtime calculated field

               

              INT([Created Time])

               

              then dragged the HHMMSS field in the filter

               

              and added the formula

               

              [CurrentTime] >= [HHMMSS_createdtime]