Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
JaviValtu
Partner - Contributor II
Partner - Contributor II

Count a field only if another field in the same row has the same value but with different origin

Hey, I have a challenge. I have this table: 

 

Capture.PNG

and I need to count (in the count_Call_Key) the Call_Key that have the Date repeat for Origin={Call} and Origin={Event}. In the example, the Row 3 must count, because {<origin={'Call'}>}Date = {<origin={'Event'}>}Date. I don´t know how to do Set Analysis inside Set Analysis. 

I have tried this but does`t work:

count({<Origin={'Call','Event'},Date={"=$(=only({<Origin={'Event'}>}Date))"}>}Call_Key)

Labels (1)
4 Replies
sunny_talwar

May be this

Count({<Origin = {'Call', 'Event'}, Date = {"=Count(DISTINCT {<Origin = {'Call', 'Event'}>} Origin) = 2"}>} Call_Key)
JaviValtu
Partner - Contributor II
Partner - Contributor II
Author

Thanks a lot, it solved the challenge, but I have realized that what I really need is this:

I need to count the Call Key, only If an Account has a Call_Key in the Date of one Event or in the five days following. And also with the same Territory.

For example,

Capture2.png

Account_Id = A1 (Territory T6) assist to an Event in Date = 1, and after that, Account_Id (Territory T6) received a Call in Date = 2 (THIS COUNT) and also received a Call in Date=9 (NOT TO COUNT).

 

Thanks again for your early answer. You help a lot!

sunny_talwar

I am not sure I follow.

JaviValtu
Partner - Contributor II
Partner - Contributor II
Author

Sorry about my explanation, but it's difficult to me to explain it. Forget all the previous comment and I will try to explain what exactly I need:

I have this table,

Capture4.png

and I have to add a new column in order to follow up if a Client receive Calls related with an Event in the following 5 days. And only considering the Comercial linked to the Client in the Event. The result I need should be this:

Capture4.png

Explanation:

  • Client X (belong to Comercial A) assist to Event E1 on 1-Oct. 
  • Client X receive a Call from commercial A on 1-Oct so the COUNT field is a YES
  • Client X receive a Call from commercial D on 4-Oct. is a NO, because Client X belongs to Commercial A
  • Client X receive a Call from commercial A on 8-Oct. Is a NO, because 8-oct is after 1-Oct + 5 = 6-Oct
  • Client X receive a Call from commercial A on 3-Oct. Is a YES, because Client X belongs to Commercial A and 3-Oct is before 1-Oct + 5
  • Client Z receive a Call from comercial A on 1-Oct. Is a NO, because client Z didn't assist to the Event.

 

Thanks a lot!!!