Skip to main content
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!!!