Forum Discussion

mhorn30's avatar
mhorn30
Frequent Visitor
6 years ago

Date on Date comparison on record statuses

I have the data of each status that an applicant has gone through as well as the data of the status change.

 

I want to be able to compare the number of applicants per status between a date last year vs. this year

 

As an example:

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

 

I challenge is that I can't just work with the date column. I need to also include the latest status per applicant as a status could have updated prior to the date selected for the report.

 

E.g. If I choose the data "01 September" it should show the following data in green, as this is the latest status reason per candidate on or before this date.

 

ProgrammeCandidate IDStatusDate
2019 Programme101Applicant26/08/2018
2019 Programme101Completed29/08/2018
2019 Programme101Reviewed01/09/2018
2019 Programme102Applicant05/09/2018
2020 Programme202Applicant29/09/2019
2020 Programme202Completed01/10/2019
2020 Programme201Applicant28/08/2019
2020 Programme201Completed02/09/2019
2020 Programme201Reviewed05/09/2019
2020 Programme201Accepted10/09/2019

 

My logic says I need something like a calculated columns with a paramter (which is the date chosen by the user), which then finds the latest record per candidate on or before the parameter date (or indicate a True/False for each record), which I can then use to create a visual per programme per status reason.

 

I'm just not sure exactly how to tackle this/where to start.

 

Any suggestions would be appreciated, thank you!

General Comment

5 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi mhorn30 ,

    I'm confused about your requirement. You want to compare the number of applicants per status between a date last year vs. this year. Moreover, you need to based on the selected date and different status per candidate, right? I can't understand why show the Candidate ID 201 in green when you choose "01 September"?  Are the 101 and the 201 the same candidate? Can you please share more details for us?

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • mhorn30's avatar
      mhorn30
      Frequent Visitor
      6 years ago

      Hi v-xuding-msft 

       

      Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

       

      The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

       

      Candidate ID's are unique, so 101 and 201 is not the same user.

       

      If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019

       

      If I was to select 29 August, then only these statusses per candidate will be returned:

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019
    • mhorn30's avatar
      mhorn30
      Frequent Visitor
      6 years ago

      Hi v-xuding-msft 

       

      Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

       

      The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

       

      Candidate ID's are unique, so 101 and 201 is not the same user.

       

      If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019

       

      If I was to select 29 August, then only these statusses per candidate will be returned:

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019
    • mhorn30's avatar
      mhorn30
      Frequent Visitor
      6 years ago

      Hi v-xuding-msft 

       

      Candidate 201 "Applicant" status is green as this is the latest status update for that candidate on or before 01 September.

       

      The next status for Candidate 201 is "Completed" which was only set on the 02 September. So if I compare YoY according to a specific date (01 Sept in this case), this status has not happened yet.

       

      Candidate ID's are unique, so 101 and 201 is not the same user.

       

      If I was to select 01 October as my comparison data YoY, then these would be the statusses per candidate that was returned as they are the latest status on or before 01 October.

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019

       

      If I was to select 29 August, then only these statusses per candidate will be returned:

       

      ProgrammeCandidate IDStatusDate
      2019 Programme101Applicant26/08/2018
      2019 Programme101Completed29/08/2018
      2019 Programme101Reviewed01/09/2018
      2019 Programme102Applicant05/09/2018
      2020 Programme202Applicant29/09/2019
      2020 Programme202Completed01/10/2019
      2020 Programme201Applicant28/08/2019
      2020 Programme201Completed02/09/2019
      2020 Programme201Reviewed05/09/2019
      2020 Programme201Accepted10/09/2019