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]

Nope.  I need to see customers who only had 2017  and nothing in 2018 in one column.  Then the next column needs to be customers who I had in both 2017 and 2018, then the next column needs to be customers who were brand new in 2018 and nothing in 2017.

Thanks

[YoussefBelloum]

Ok here you will need to use the P() and E() on the set analysis.

for 2017 only:

count( {$<ServiceLocation = E({1<ContractStart_Year={2018}>})>} ServiceLocation )

this expression returns the number of Servicelocations, but only those ServiceLocations that never been present in the year 2018.

Please find attached

[tmumaw]

getting close.  There are too many in 2017 and 2018.  if you sort the Opportunity by servicelocation you will see how they should be group.  thanks

[YoussefBelloum]

EDITED

I didn't find better solution for the 2nd expression (2017+2018)

=count(DISTINCT {$<ServiceLocation = P({1<ContractStart_Year={'2017'}+{'2018'}>} )>} ServiceLocation)

-

count(distinct {$<ServiceLocation = E({1<ContractStart_Year={2018}>})>} ServiceLocation )

-

count( {$<ServiceLocation = E({1<ContractStart_Year={2017}>})>} ServiceLocation )

I'm pretty sure stalwar1‌ have better solution using p()..

stalwar1‌ I'll try to explain when I tag you on something, so here he want to count the ServiceLocations which are present simultaneously in 2018 AND 2017.. which means exclude the ones that are present only in 2017 and exclude the ones that are present only in 2018.. I didn't find better solution.

[sunny_talwar]

May be this for both the years

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

Another way to right this would be this

Count({$<ServiceLocation = {"=Count(DISTINCT {<ContractStart_Year={'2017', '2018'}>} ContractStart_Year) = 2"}>} ServiceLocation)

[YoussefBelloum]

Very nice, very beautiful, very sunny

here you are Mr Thom.. just choose the solution you want and let us know

[tmumaw]

Hi Sunny,

Here is what the counts should be :

2017 Only   3

2017 and 2018  11

2018 only 1

Hope this helps.

[tmumaw]

Yes very nice and very...very close

[YoussefBelloum]

the counts are already ok

choose the expression you want if you want to replace it

PFA !

[sunny_talwar · Accepted solution]

Just do a DISTINCT count

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