Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Data Trim Based on changing date per user

Hello

Looking for a few ideas to handle a data trim operation

Two tables for a helpdesk report:

I have incident data exported in an .xls tab that has an incident number (Ticket#) and a user number (ID) (TICKETS DATA)

In a second tab, I have a subset of the users (ID) number and a date (START DATE)  (ProjectDATA)

 

What I want to do is show only the Ticket# for people that have a matching ID in PROJECT but also fall within a certain date range (15 days before and 15 days after) their START DATE in ProjectDATA.   

So basically we wind up with only incident data that is a 30 day view of tickets before and after a specific start date, but that START DATE could be different for each user.   


(One has startdate of 5/1,  i want tickets from April 16 to  May 15 for that user;   a second starts on mAY 16 so I want ticket# from May 1 to May 30 for them, etc etc...)


 What do ya have for me?

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    8 years ago

    Anonymousif you can share sample data in excel sheet, will get you the solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Simple data

       

      So for two users in Project Data i would only want to see those entries in Tickets that are within 15 days of the Email1 date in Project Data for each user.  There would be 2 for bkantesa and 3 for malko left based on the data below.

       


      Project Data

      IDUser NameEmail1 DateEmail2 DateEmail3 DateState
      bkantesaKento, Barto11-May-18--NJ
      malkoMitch, Sam1-Jun-18--NJ

       

       

      Tickets

      KBSubmit DateProduct NameStatusID
       4/10/2018 0:00Microsoft Active DirectoryCanceledbkantesa
      KBA000364831/1/2018 0:40iPadClosedahujaak
      KBA000898176/1/2018 0:00Office 365Closedmalko
      KBA000098755/20/2018 0:00Microsoft Active DirectoryClosedmalko
       6/4/2018 0:00Android PhoneClosedmalko
       1/1/2018 1:05Office 365Closedjonnalla
       5/1/2018 0:00PartnerNetClosedbkantesa
       1/1/2018 1:43myLEARNINGClosedharryn
      KBA000393041/1/2018 2:08PNetClosedrangasmu
       5/20/2018 0:00Office 365Closedbkantesa
      KBA000925383/1/2018 0:00ChromeClosedmalko