4 Replies Latest reply on Jun 5, 2017 10:24 AM by Daniel Vincent

    Count if Within X Days of Y Date, from separate Tables

    Adam Rauh

      Hey all,

       

      I know I'm probably just missing a paranthesis somewhere, so wanting another pair of eyes. 

       

      I'm trying to count if tickets have been submitted w/in 14 days of a new hire's start date.  We use 2 separate tables for these requests -

       

      • RITM ('Due Date') for the new hire's start date (so this would be the anchor date to set w/in 14 days of)
      • INC ('Created Date') for any incidents created (variable for if w/in 14 days of REQ's due date)

       

      Below is what I was using:

       

      IF ATTR([Opened At]) < ATTR([SC RITM].[Due Date]) - 14 + 1 THEN 1

      ELSE 0

      END

       

      But I'm getting 0's when I add it to the spreadsheet.  Data relationship is fine (RITM's New Hire ='s INC's Caller ID, since I want to use the RITM as my primary data source), so not sure if I'm just not adding something to the view to count, my formula is incorrect, etc.

       

      Bonus points if we can w/in the filter limit to the New Hire to the Caller ID.  I was just planning on doing that via filter but if we can get it in the calculated field that'd be great (figured we could w/ an 'And', but since I couldn't get the above working I haven't gotten that far).

       

      Thx,
      Adam

        • 1. Re: Count if Within X Days of Y Date, from separate Tables
          Abhishek Yadav

          Hi Adam,

           

          You can get whether the date is within 14 days or not by using  'DATEDIFF('day',ATTR([Due Date]),MAX([Start Dates].[Opened At]))'. As there are more than 1 date for 1 caller id so we will get '*'. Thta's why I have used as max of date. And then you can filter the result to display only less than 14days record. Please check the screenshot and let us know is this you were trying to achieve?Community.PNG

          • 2. Re: Count if Within X Days of Y Date, from separate Tables
            Adam Rauh

            I think you're on the right track, but it's not returning what I would expect:

             

            Ex,

             

            For John Doe, we have the below REQ/start date ("Due Date"):

             

               

            REQ #Due DateNew Hire
            REQ1231/1/2017John Doe

             

            Then, from our INC table, we see:

             

               

            INCOpened AtCaller ID
            INC1231/1/2017John Doe
            INC1241/1/2017John Doe
            INC1421/20/2017John Doe
            INC1431/21/2017John Doe
            INC1441/22/2017John Doe

             

            So, what I would expect to see is "2", as in he's had "2" incidents submitted w/in 14 days of his start date (5 listed, but the other 3 are <14 days).  It seems using the above method, it's just agg'ing the date difference b/t each incident together, instead of telling me each individual incident's relation date to his start date, then I could use filter's / countd's.

             

            Hope this helps,

            A

            • 3. Re: Count if Within X Days of Y Date, from separate Tables
              Abhishek Yadav

              Hi Adam,

               

              You can use a calculated field to count the number of inc and formula is : '

              if DATEDIFF('day',ATTR([Due Date]),

              MAX(([INC (Start Dates)].[Opened At])))<14

              THEN COUNT([INC (Start Dates)].[INC]) end

              '

              Check the screenshot.

              Count.PNG

               

              Thanks,

              Abhishek

              • 4. Re: Count if Within X Days of Y Date, from separate Tables
                Daniel Vincent

                Without knowing how you want things laid out the below is the simplest example (uploaded a 10.0.3 workbook ex.)

                 

                WINDOW_SUM(IF DATEDIFF('day',ATTR([SC RITM (Start Dates)].[Due Date]),MAX([Opened At]))<=14 THEN 1 END)

                 

                Screen Shot 2017-06-05 at 12.23.32 PM.png