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]);

[YoussefBelloum]

Hi,

why don't you concatenate and use ONE flag field instead of joining and using different Flag fields ?

[tmumaw]

Because I have to join to the Account to determine if it's a new account, existing, or lapsed.

[YoussefBelloum]

Actually, that was my question.. why don't you concatenate instead of join ?

example (maybe I'm missing something)

Concatenate (Account)
LOAD DISTINCT [ServiceLocation],
'OLD' AS FLAG
RESIDENT Opportunity
WHERE  ContractStart_Year < '$(vCYear)';

Concatenate (Account)
LOAD DISTINCT [ServiceLocation],
'NEW' AS FLAG
RESIDENT Opportunity
WHERE  ContractStart_Year = '$(vCYear)' ;

[tmumaw]

I can see the first concatenate working for old customers 2017, but the second concatenate would give me anybody who had a contract in 2018.  What about the ones that had a contract in both 2017 and 2018?  I tried it and it returned null values in FLAG

[YoussefBelloum]

Hi Thom,

I was trying to suggest a better solution and I don't have the details..

If it is not necessary for you to join, then you can try other solutions (a concatenation for example) and see the difference by yourself.

Let me just ask a question, why do you need to create flags for old and new customers? the year field is not enough ?

[tmumaw]

Youssef,

Thank you very much for your help.  I really do appreciate it.  I have a request from the user requires me to count customers by sales office.  I need to show the number of customers which only had sales in 2017,  then the number of customers who had sales in 2017 and 2018, and the last column would be the new customers which only had sales in 2018.  The pivot table needs to look like this:

     office                      2017 Only Customers       2017 and 2018  Customers         2018 New Customers

123-Chicago                               12                                     35                                                5

[YoussefBelloum]

you're welcome Sir, we are all here trying to help.

thank you for these informations.

Here I don't think you need to join or to concatenate, the Year field is enough to make these calculations..

But to confirm I will need you to attach some rows from your table (or some sample rows which is the equivalent of your table structure) or a screen shot of your table structure, to be able to populate a dataset and make an example app for you.

[tmumaw]

Here is some sample data.  Please let me know if you have any questions.  Thanks

[YoussefBelloum]

is this what you need ?

Please find attached the app