Forum Discussion

aymeric_kolan's avatar
aymeric_kolan
Frequent Visitor
9 years ago
Solved

Identify row with same ID (condition: 24 hours)

Hi all,

 

I’m working on IVR data for customer service and I’m looking for identify phone number who try to contact us more than once in 1 day…

For each rows, I need to know if there is a row with the same InboundVoiceCalls_CallId (ID) in the next 24 hours.

Columns :

- calltime (date and hours)

- InboundVoiceCalls_CallId (ID)

- CallCLID (phone number)

 

For example, you can see in colums “reit 1d” :

  • 1: It’s because I have one row 24 hours after with same ID
  • 0: there is no row with same ID

 

CallTime

InboundVoiceCalls_CallId

CallCLID

Réit1d

02/01/2017 10:03

EI227-C64756

5776638

1

02/01/2017 10:14

EI227-C64901

5776638

1

02/01/2017 17:05

EI228-C2453

5776638

1

02/01/2017 17:17

EI228-C2516

5776638

0

02/02/2017 09:15

EI228-C18825

5776638

0

09/02/2017 11:42

EI228-C21617

5776638

1

09/02/2017 11:47

EI228-C21625

5776638

1

09/02/2017 15:22

EI228-C21850

5776638

0

07/03/2017 16:52

EI230-C4967

5776638

0

31/03/2017 17:02

EI230-C10444

5776638

0

03/04/2017 13:55

EI230-C10642

5776638

1

04/04/2017 10:37

EI230-C10903

5776638

0 (there is more than 24 hours w/ next row)

05/04/2017 11:33

EI230-C11292

5776638

0

05/04/2017 15:16

EI230-C11389

5776638

0

 

 

I tried to do a calculated column:

 

Réit1j = IF(

    COUNTROWS(

        FILTER(

            'DATA SVI_2';

            'DATA SVI_2'[CallCLID] = EARLIER('DATA SVI_2'[CallCLID]) &&

            'DATA SVI_2'[CallTime].[Année] = EARLIER('DATA SVI_2'[CallTime].[Année]) &&

            'DATA SVI_2'[CallTime].[Mois] = EARLIER('DATA SVI_2'[CallTime].[Mois]) &&

            'DATA SVI_2'[CallTime].[Jour] = EARLIER('DATA SVI_2'[CallTime].[Jour]) - 1

        )

    ) > 0;

    TRUE;

    FALSE

)

 

Do you have some ideas how I can do that?

Thank you for your help!

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    aymeric_kolan

     

    Hi,

     

    Please try this formula as a calculated column. If you want to change the time duration, just change the DeadLine part.

    Réit1j =
VAR DeadLine = 'DATA SVI_2'[CallTime] + 1
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'DATA SVI_2' ),
            FILTER (
                'DATA SVI_2',
                'DATA SVI_2'[CallTime] > EARLIER ( 'DATA SVI_2'[CallTime] )
                    && 'DATA SVI_2'[CallTime] <= DeadLine
                    && 'DATA SVI_2'[CallCLID] = EARLIER ( 'DATA SVI_2'[CallCLID] )
            )
        )
            >= 1,
        1,
        0
    )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi aymeric_kolan,

     

    I have a few questions.

    1. “a row with the same InboundVoiceCalls_CallId (ID)”. I can’t see any rows with the same InboundVoiceCalls_Callid(ID) in the sample.
    2. “more than once in 1 day” and “in the next 24 hours”. Do you mean 00:00:00 to 23:59:59 or the next 24 hours from a special time.

    If these questions were clarified, the formula could be easy.

     

    Best Regards!

    Dale

    • aymeric_kolan's avatar
      aymeric_kolan
      Frequent Visitor
      9 years ago

      Hi  v-jiascu-msft

       

      1. You're right. In fact, InboundVoiceCalls_CallId is a system ID but we have to check on CallCLID (CallCLID is the phone number).

      2. Good question, I think we need to look the day after.

      For example, if a have a row at 10:00:00 on Monday, we want to check until 23:59:59 on Thusday and have 1 if there is another row with same CallCLID at 18:50:00 on Thusday.

       

      Thank you ! :)

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        aymeric_kolan

         

        Hi,

         

        Please try this formula as a calculated column. If you want to change the time duration, just change the DeadLine part.

        Réit1j =
VAR DeadLine = 'DATA SVI_2'[CallTime] + 1
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'DATA SVI_2' ),
            FILTER (
                'DATA SVI_2',
                'DATA SVI_2'[CallTime] > EARLIER ( 'DATA SVI_2'[CallTime] )
                    && 'DATA SVI_2'[CallTime] <= DeadLine
                    && 'DATA SVI_2'[CallCLID] = EARLIER ( 'DATA SVI_2'[CallCLID] )
            )
        )
            >= 1,
        1,
        0
    )

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale