Logic Help

[tmumaw]

Hi All,

Need a little help with some logic.  I need to report on customers who had a contract in 2017, customers which had contracts in 2017 and 2018, and customers which had contracts in 2018 only.  I got the customers which had contracts in 2017 and 2018, and the ones who only had contracts in 2018.  However I am having issues getting the customers which only had contracts in 2017.  Any ideas?  Here is my script which works for part of my answer.

thanks

Left Join (Account)
LOAD DISTINCT [ServiceLocation],
[ServiceLocation] AS [Old ServLoc],
1 AS Old_ServLoc
RESIDENT Opportunity
WHERE  ContractStart_Year < '$(vCYear)';

//New_ServLoc:
Left Join (Account)
LOAD DISTINCT [ServiceLocation],
1 AS New_ServLoc
RESIDENT Opportunity
WHERE  ContractStart_Year = '$(vCYear)'
AND NOT EXISTS([Old ServLoc], [ServiceLocation]);

[tmumaw]

Count({$<ServiceLocation = P({1<ContractStart_Year={'2017'}>})*P({1<ContractStart_Year={'2018'}>})>} distinct ServiceLocation )

I added a distinct and it works......

[sunny_talwar]

Yes sir

[tmumaw]

Thank you very much for your help.  I would have been looking at this for quite sometime before I figured it out.  Now my next question is you both helped....who is correct.....LOL

[YoussefBelloum]

So you can close the thread by marking the correct and the helpful answers

[sunny_talwar]

youssefbelloum‌ - I was not even helping you here... I just came in because he called out . So, he deserves it the most.

Best,

Sunny

[YoussefBelloum]

Don't worry about the points choose the best solution for you and mark it as correct. you can also mark as much answers as you want as helpful if they are helpful for you

Good luck