5 Replies Latest reply on Sep 23, 2016 6:54 AM by swaroop.gantela

# Calculating First and Last Dates

Hi,

I'm a bit of a newbie here so please excuse me if this question is obvious...

I've got two CSVs, one containing enrollment data (one appointment per row) and the other a client database. I'm trying to calculate per staff member the number of first appointments, last appointments, total clients and failed conversion.

I joined them in the data section and was able to make this chart:

What I'm trying to figure out is how to use Tableau to create something that looks like this (which I entered manually into a spreadsheet as an example):

So first appointments are when there is no enrollment from previous months, last appointments are when there is no enrollment in following months, and failed conversions are first and last in the same month.

Please let me know if you can help with this, thanks!

I attached the sample workbook.

Alex

• ###### 1. Re: Calculating First and Last Dates

Alex,

Maybe more complicated than need be, but please see if the attached could be a first step.

It first uses Level of Detail calculations to flag the first,last, and failed.

Here it is for first:

[Service Date (Months)]={ FIXED [Staff Name],[Client ID]:MIN([Service Date (Months)])}

Then to Count the Firsts, I used:

WINDOW_SUM(MIN(IF [FirstAppt] THEN 1 ELSE 0 END))

the Min is because of the potential multiple visits in a month.

There is a particular combination of "Compute using" and Restart.

• ###### 2. Re: Calculating First and Last Dates

Wow, that looks great!

One thing I'm not sure I understand involves our client that changes staff

members.

It looks like it's counting jen prink's first appt with casey and her first

appt with peter as first appts, but its not counting her last appt with

casey as a last appt and thus a failed conversion... Is there a way to

tweak it to get it to do this?

Alex

On Thu, Sep 15, 2016 at 7:53 PM, swaroop.gantela <

• ###### 3. Re: Calculating First and Last Dates

Alex,

Please see if the attached is closer.

I added some extra LOD to the Failed check so it now reads:

INT({ FIXED [Staff Name],[Client ID]:SUM([FirstAppt])}={ FIXED [Staff Name],[Client ID]:SUM([LastAppt])})

trying to take into account the staff and the client.

• ###### 4. Re: Calculating First and Last Dates

Hi, this works well on the sample data (which looks like it had an error),

how do I replace it with my real data?

Thanks!

Alex

On Fri, Sep 16, 2016 at 9:14 PM, swaroop.gantela <

• ###### 5. Re: Calculating First and Last Dates

Alex,

Sorry I didn't quite catch.

In what way is the form of your real data