topic Re: Count a field only if another field in the same row has the same value but with different origin in App Development

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

JaviValtu — Sat, 16 Nov 2024 19:38:39 GMT

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

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)

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

sunny_talwar — Tue, 19 Nov 2019 12:05:19 GMT

May be this

Count({<Origin = {'Call', 'Event'}, Date = {"=Count(DISTINCT {<Origin = {'Call', 'Event'}>} Origin) = 2"}>} Call_Key)

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

JaviValtu — Tue, 19 Nov 2019 12:27:53 GMT

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,

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!

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

sunny_talwar — Tue, 19 Nov 2019 12:36:59 GMT

I am not sure I follow.

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

JaviValtu — Tue, 19 Nov 2019 14:35:45 GMT

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,

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:

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!!!