5 Replies Latest reply on Dec 5, 2018 3:30 AM by Amit Narkar

# How to get the record value by comparing dates from a single field

Hello Everyone,

I am new here

I have a list of people ID who has attended training from 2014 to 2018. I need to get details of number of people who has not attended training in 2017 and attended in 2018.

i filtered the year ("Start date") to 2017 and 2018. and used no of records to check how many people attended.

Now how do i get number of records from this list that who has attended training in 2018 and not in 2017?

i tried this in the calculation. but it shows Null value.

``SUM(IF ISNULL(YEAR([Start Date])=2017) AND NOT ISNULL(YEAR([Start Date]) =2018) THEN 1 END)``

`Any help is much apprecited. I have attached a mock up excel data to give you some idea. Best Regards,D`
• ###### 1. Re: How to get the record value by comparing dates from a single field

could you confirm regarding expected result?

• ###### 2. Re: How to get the record value by comparing dates from a single field

Dhivya,

You can use LOD for this:

Create calculated field T|F as

{fixed [Participant ID] : max(IF YEAR([Start Date]) =2018 then 1 else 0 end)}=1

and

{fixed [Participant ID] : min(IF YEAR([Start Date]) =2017 then 1 else 0 end)}=0

create another field as

countd(if [T|F] then [Participant ID]  end)

This will give you the desired value.

Thanks,

AB

• ###### 3. Re: How to get the record value by comparing dates from a single field

Hi Amit,

Thank you very much for the response. I checked with the excel match value. It shows 70. The above one showing 113 is wrong. You can see in the attached workbook of third sheet. I have made a simple match value of IDs that are not present in 2017 and present in 2018.

• ###### 4. Re: How to get the record value by comparing dates from a single field

hi I think the calculation is wrong. I want to get records which has value present only in 2018 and should be present in 2017, if its present in both 2017 and 2018 i dont want to include the record. I have highlighted the yellow for your understanding.

• ###### 5. Re: How to get the record value by comparing dates from a single field

You will get different results based on number of records and countd (participant id)

1 of 1 people found this helpful