Where Contract Start = Contract End +1

[Anonymous]

Hi All,

I have 3 fields ID, CONTRACT START YEARMONTH and CONTRACT END YEARMONTH.

I want to count the Distinct IDs where CONTRACT START YEARMONTH = the Selected value for CONTRACT END YEARMONTH.

In short it counts IDs where the Contract has been renewed.

I can get the values ok when I specify a specific yearmonth but I'd prefer if it was based on the selection itself:

COUNT(DISTINCT{<CONTRACT_START_DTTM_YEARMONTH ={'2018 Jan'}, CONTRACT_END_DTTM_YEARMONTH = {'2017 Dec'}>}ID) 

Cheers,

[Anonymous]

Amendment:

I want to count the Distinct IDs where CONTRACT START YEARMONTH = the Selected value for CONTRACT END YEARMONTH+1

[jyothish8807]

Hi Tim,

Try like this:

vConEnd= date(addmonths(GetFieldSelections([CONTRACT END YEARMONTH]),1),'YYYY MMM')

COUNT(DISTINCT{<CONTRACT_START_DTTM_YEARMONTH ={'2018 Jan'}, CONTRACT_END_DTTM_YEARMONTH = {'$('vConEnd)'}>}ID)

[sunny_talwar]

When you say +1, do you mean one day or 1 month? Also, it might help to see any an example data set and the output you expect to see from it

[Anonymous]

Hi Sunny,

+1 should be one month, sample data below:

ID	CONTRACT START YEARMONTH	CONTRACT END YEARMONTH
1	2015 Jan	2015 Dec
1	2016 Jan	2016 Dec
1	2017 Jan	2017 Dec
1	2018 Jan	2018 Dec
2	2016 Jan	2016 Dec
2	2017 Jan	2017 Dec
3	2017 Jan	2017 Dec
4	2017 Jan	2017 Dec
5	2017 Jan	2017 Dec
5	2018 Jan	2018 Dec
6	2016 Jan	2016 Dec
6	2017 Jan	2017 Dec
6	2018 Jan	2018 Dec
7	2018 Jan	2018 Dec
8	2018 Jan	2018 Dec
9	2016 Jan	2016 Dec
9	2017 Jan	2017 Dec
9	2018 Jan	2018 Dec
10	2015 Jan	2015 Dec
10	2016 Jan	2016 Dec

  If I wanted to count the Number of renewals for contracts that Ended 2017 Dec. I would be looking for sites with this end date but with a further Contract start date of 2018 Jan .  In the example above it should return IDs, 1,5,6 9 .

As I mentioned in my initial post, COUNT(DISTINCT{<CONTRACT_START_DTTM_YEARMONTH ={'2018 Jan'}, CONTRACT_END_DTTM_YEARMONTH = {'2017 Dec'}>}ID) returns this for me but I would like my expression to be dependent on the Contract End Date selected by the user.

Thanks,

Tim

[sunny_talwar]

Try this

=Count(DISTINCT{<ID = P({<[CONTRACT START YEARMONTH] = {"$(=Date(MonthStart(Max([CONTRACT END YEARMONTH]), 1), 'YYYY MMM'))"}, [CONTRACT END YEARMONTH]>})*P({<[CONTRACT END YEARMONTH] = {"$(=Date(Max([CONTRACT END YEARMONTH]), 'YYYY MMM'))"}>}), [CONTRACT END YEARMONTH]>}ID)