Count of customers based on type of contact

[alexish]

Hello,

I'm loooking for help in solving a tricky (for me...) issue that I have.

Context :

We have customers, that are "visited" either Face-To-Face, or remotely (we call this "channel")

We track all those interactions, and the "channel" that is used.

See an example file included.

What I'm trying to do :

We would like to identify, by month, the number of Unique Customers, that have been visited F2F, or Remote... Or if both, "Hybrid".

Constraints : I can't modify the script here, I'm going to need a solution without it !

What I've done : (and that almost works...)

I've created 3 measures that look like this :

COUNT( DISTINCT
    {< CUSTOMER_ID = {"=
           COUNT({<INTERACTION_CHANNEL = {'Face-To-Face', 'INPR'}>}CUSTOMER_INTERACTION_CODE) > 0
           AND COUNT({<INTERACTION_CHANNEL = {'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) = 0
           "}
    >}
CUSTOMER_ID)

Where the red part changes : if ">" &  "=" : F2F interactions, if "=" then ">" : Remote, if both are ">", it's "Hybrid"

See qvf included

Where I have issues :

Left table is raw data

Right table is what i'm trying to achieve

It seems in my right table that the formula does not contextualize with the date, and I'm not sure why...

For example, for date 2/15/2020, I should have F2F=2  since Customer C2 & C7 have F2F interactions.

But since C2 has 1 interaction in Remote on 1/15/2020, it's displayed in hybrid. If I filter my date, the tables now works fine.

And I can't seem to find how to get the formula right. (or if I shoudl change totally the way I approach this)

Could you please help me ?

Thanks everyone

[alexish]

Hello,

Thanks a lot @NitinK7  ! This is what we were looking for !

@tresescoIndeed INPR is F2F too (i think it was in my QVF, maybe I should have made that more clear), but i checked your solution and this was not the cause of the error anyway !

For your information, we are trying a variation of what was proposed, like this :

=SUM(

AGGR(

                            IF(

                                          COUNT({<INTERACTION_CHANNEL={'Face-To-Face','INPR'}>}CUSTOMER_INTERACTION_CODE) > 0

                                          AND

                                          COUNT({<INTERACTION_CHANNEL={'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) = 0,

                                          1

                            ),

    CUSTOMER_ID,

              DATE)

)

But in the end the result is the same, and we couldn't have found it without you!

Once again, thanks a lot to both of you, I will mark the topic as resolved