topic Re: Where Contract Start = Contract End +1 in QlikView

Where Contract Start = Contract End +1

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Where Contract Start = Contract End +1

Anonymous — Mon, 21 May 2018 10:25:38 GMT

Amendment:

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

Re: Where Contract Start = Contract End +1

jyothish8807 — Mon, 21 May 2018 10:35:37 GMT

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)

Re: Where Contract Start = Contract End +1

sunny_talwar — Mon, 21 May 2018 11:27:04 GMT

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

Re: Where Contract Start = Contract End +1

Anonymous — Mon, 21 May 2018 13:46:27 GMT

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

Re: Where Contract Start = Contract End +1

sunny_talwar — Mon, 21 May 2018 13:55:11 GMT

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)