Voicemails returned

[Vtomsons]

Hi,

I have a sample of the data I am working with. Result I am trying to produce is how many voicemails have been returned from a sales activity log with these columns (data set is thousands long so I have isolated one "contact" that had a returned voicemail. Trick is I would like the result to appear on the row that the voicemail occured (as I have multiple Campiagns "Case Subjects" that voicemails could be logged.

I would like to create a new column "voicemail returned" that would return TRUE for only the message type "Outgoing" and Phone results with "voicemail". Ideally it would look for a message type of "Incoming" that occured after the "sent Date" of the original outgoing voicemail. 

This is probably simple for some of you out there but way beyond my skillset...thanks in advance for any guidance.

Contact	Message Type	Channel	Phone Results	Case Message Subject	Case Subject	Sent Date
Kate Gowen	Incoming	Email		DOHS discount payment	Gowen DOHS	16/08/2022 08:14 AM
Kate Gowen	Outgoing	Email		DOHS discount payment reply	Gowen DOHS	16/08/2022 08:14 AM
Kate Gowen	Outgoing	Email		acceptance letter	Gowen DOHS	15/08/2022 04:03 PM
Kate Gowen	Outgoing	Email		Registration reply	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	15/08/2022 03:23 PM
Kate Gowen	Incoming	Email		Ready to register	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	15/08/2022 03:18 PM
Kate Gowen	Outgoing	Phone	Voice Mail	Left Voicemail. No Consent to Email	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	02/08/2022 02:57 PM
Kate Gowen	Outgoing	Phone	Voice Mail	Left Voicemail. No Consent to Email	2022 May DOHS Information Session Follow-up	20/07/2022 03:35 PM

[lbendlin]

please indicate the expected result based on the sample data.  Which of the rows should be tagged?

[Vtomsons]

Hi,

Line 6 (because it has phone result of "voicemail") would have an additional column indicating (true/false) that at a later sent date an incoming message type from the same student (contact) with the same case subject (campaign name) was logged. Line 7 would be an example of false as although there are later incoming message types, none from the specific case subject (2022 May DOHS Information Session Follow-up).

My data set would have many different contacts and case subjects to analyze. Really appreciate your insight and if you have any suggestions/ideas.

Added the result below for voicemail returned column and the row that would yield a positive/negative result.

Thanks

Contact	Message Type	Channel	Phone Results	Case Message Subject	Case Subject	Sent Date	Voicemail Returned?
Kate Gowen	Incoming	Email		DOHS discount payment	Gowen DOHS	16/08/2022 08:14 AM
Kate Gowen	Outgoing	Email		DOHS discount payment reply	Gowen DOHS	16/08/2022 08:14 AM
Kate Gowen	Outgoing	Email		acceptance letter	Gowen DOHS	15/08/2022 04:03 PM
Kate Gowen	Outgoing	Email		Registration reply	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	15/08/2022 03:23 PM
Kate Gowen	Incoming	Email		Ready to register	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	15/08/2022 03:18 PM
Kate Gowen	Outgoing	Phone	Voice Mail	Left Voicemail. No Consent to Email	2022 OHS/CPSL 30% Discount Non-Consent Follow-UP	02/08/2022 02:57 PM	True
Kate Gowen	Outgoing	Phone	Voice Mail	Left Voicemail. No Consent to Email	2022 May DOHS Information Session Follow-up	20/07/2022 03:35 PM	False

[lbendlin · Accepted solution]

calculated column: 

VM Ret = 
if([Phone Results]="Voice Mail",
    var c = [Contact]
    var cs = [Case Subject]
    var sd = [Sent Date]
    var r = calculate(COUNTROWS('Table'),ALL('Table'),'Table'[Contact]=c,'Table'[Case Subject]=cs,'Table'[Message Type]="Incoming",'Table'[Sent Date]>sd)
    return if(r>0,"True","False"))

see attached.