topic Re: Logic Help in QlikView

Logic Help

tmumaw — Tue, 13 Mar 2018 15:47:01 GMT

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

Re: Logic Help

YoussefBelloum — Tue, 13 Mar 2018 15:58:10 GMT

Hi,

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

Re: Logic Help

tmumaw — Tue, 13 Mar 2018 17:03:12 GMT

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

Re: Logic Help

YoussefBelloum — Tue, 13 Mar 2018 17:15:23 GMT

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

Re: Logic Help

tmumaw — Tue, 13 Mar 2018 18:37:42 GMT

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

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 08:59:36 GMT

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 ?

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 11:05:38 GMT

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

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 11:19:26 GMT

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.

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 12:28:23 GMT

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

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 13:15:24 GMT

is this what you need ?

Please find attached the app

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 13:26:32 GMT

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

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 13:56:57 GMT

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

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 14:05:39 GMT

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

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 14:28:15 GMT

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.

Re: Logic Help

sunny_talwar — Wed, 14 Mar 2018 14:37:05 GMT

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)

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 14:40:49 GMT

Very nice, very beautiful, very sunny

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

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 14:42:32 GMT

Hi Sunny,

Here is what the counts should be :

2017 Only 3

2017 and 2018 11

2018 only 1

Hope this helps.

Re: Logic Help

tmumaw — Wed, 14 Mar 2018 14:43:57 GMT

Yes very nice and very...very close

Re: Logic Help

YoussefBelloum — Wed, 14 Mar 2018 14:44:17 GMT

the counts are already ok

choose the expression you want if you want to replace it

PFA !

Re: Logic Help

sunny_talwar — Wed, 14 Mar 2018 14:44:41 GMT

Just do a DISTINCT count

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