7 Replies Latest reply on Jul 30, 2018 2:28 PM by Steve Martin

Calculating Face-to-Face, Meetings or Both?

I have a set of data that lists customer contacts by date and type (Face-to-Face or Meeting).

I want to be able to look across a given time period and see how many customers have been seen only Face-to-Face; only at a Meeting or they have been seen at BOTH event types.

As my data holds customer info, I can't post a workbook but, hopefully, you can figure the answer theoretically.

Thanks,

Wilf

• 1. Re: Calculating Face-to-Face, Meetings or Both?

Create a meeting type parameter, MeetType,  that has values of Face to Face, Meeting and Both.  Put that on the sheet and then create a calculated field called meetingFilter.  If these values are actual values in your data, then set the calculated field to [MeetType] and place it on the Filters Shelf.  When you change the parameter value, the filter will follow.

• 2. Re: Calculating Face-to-Face, Meetings or Both?

Hi Tim,  thanks for replying so quickly.  Unfortunately, this does not answer my question (maybe it wasn't clearly stated).  What I want is a count of the number of customers in each group, not a filtered list.

• 3. Re: Calculating Face-to-Face, Meetings or Both?

Hi Wilf,

If I have understood you, what you want is a time-based cohort analysis:

So:

• drag the time period onto the columns, and
• plot either sum(customer name) or count(customer name) onto the rows to create a time based chart
• Finally, drag the type to the colours to split your line by each of the meet types

Failing this, we are going to need a little more help understanding the requirements. Granted, you cannot use company data and we will implore you not to, but you can and we would prefer it if you were to use Superstore Sales to mock this as this gives us a starting point.

Also, you could mock something in Excel, this way, it helps everyone (including you) to get to the crux of the requirement.

Steve

• 4. Re: Calculating Face-to-Face, Meetings or Both?

Hi Wilf,

If I've understood you right you're looking to segment customers depending on the types of meetings they have had?

Do you only have the two meeting types?

You can do this using a fixed calculation on a customer ID to determine if they have had multiple meeting types, and if not return the type they have had with the following.

I've attached an example using some (pretty bad) example data I generated in Excel.

• 5. Re: Calculating Face-to-Face, Meetings or Both?

Hi Wilf,

This can be done in different ways depending on what you want to do with the results. Without seeing any example data, I'll provide a general guidance which you can tweak to meet your needs.

[# Face-to-Face only]

{FIXED [Customer], [Date]: IF COUNTD([Type]) = 1 AND MIN([Type]) = 'Face-to-Face' THEN 1 ELSE 0 END}

[# Meeting only]

{FIXED [Customer], [Date]: IF COUNTD([Type]) = 1 AND MIN([Type]) = 'Meeting' THEN 1 ELSE 0 END}

[Both]

{FIXED [Customer], [Date]: IF COUNTD([Type]) = 2 THEN 1 ELSE 0 END}

Hope this helps.

Ossai

• 6. Re: Calculating Face-to-Face, Meetings or Both?

Thanks Chris - that's the one I needed .  It's the first time I've seen and IF statement in a FIXED calc, which is great!

• 7. Re: Calculating Face-to-Face, Meetings or Both?

Hi Wilf,

Please can you mark Chris's post as correct so the thread can close.

Steve