4 Replies Latest reply on Oct 9, 2019 3:12 PM by John Sarantos

# Number of missed classes with just a Start Date and Percentage

I'm using Tableau 2019.2 and workbook is attached along with the data source.

I need to find students that have missed 3 or more classes and all I have is a start date, student's percentage out of 100. The number of missed classes will be checked throughout the term so the number of days a class was held and student's percentage will change depending when it is run. The class is held for 8 weeks and only Monday to Thursday.

I need to figure out how many days a class was held so far before it ends (it will also probably be run after it ends) so I can use the student's percentage to figure out number of missed classes.

1. To get the "End Date" that seems to work, I did "DATE(DATEADD('week',8,DATETRUNC('week',[Start Date], 'Thursday')))."

2. I need to exclude holidays so they are not counted in the number of days a class is held. I have a holiday calculated field that can be subtracted.

Excel file in the workbook

Nbr of Days (needed calculated field)Term (string)Class (string)
Possible (string)Start Date (datetime changed to date)End Date (calculated field)ID (string)Student Percentage (string)
32 minus 2 holidays=30 (class is over)1194TEST 44-1100.01/7/2019  11:06:00 PM2/28/2019192.0
32 minus 2 holidays=30 (class is over)1196TEST 61-1100.05/20/2019  1:37:36 PM7/11/2019189.0

1. Run today: 25 days minus 1 holiday=24

(class is not over so that is why only 25 days Monday to Thursday have been held so far)

2. If run on Monday, August 12th: 26 days minus 1 holiday=25

1196TEST 63-1100.06/27/2019  2:16:12 PM8/22/2019193.0

I've been trying to get this to work for days with no luck so help is very APPRECIATED!  Thank you!

• ###### 1. Re: Number of missed classes with just a Start Date and Percentage

Hi Cathy,

Here's one approach.  First, I would recommend creating a date scaffold, which will give you at least one row per day.

Here's a post that lists several add'l posts about it: Data Scaffolding in Tableau

Here's a blog post that also might be helpful: https://www.kenflerlage.com/2019/03/date-scaffold.html

Step One: Create a Workbook in Excel that has all the days of 2019

Step Two: Join that to your existing data with Created Join calcs of 1=1

Step Three:  Create a calculated field to filter to only relevant dates

Applicable Dates Filter

[Date]>=[Start Date]

AND

[Date]<=[End Date]

Step Four:  Drag Applicable Dates Filter to Filters card and set to 'True'

Step Five: Create a Calculated Field to get the correct count of  Monday-Thursday days possible, to date

M-Thursday Count

iif(today()>=[End Date],

iif(datepart('weekday',[Date])<>1 AND datepart('weekday',[Date])<6,1,0),

iif([Date]<=TODAY(),iif(datepart('weekday',[Date])<>1 AND datepart('weekday',[Date])<6,1,0),0))

Step Six: Create Actual Class Length to Date Calculated field

Actual Class Length to Date

SUM([M-Thursday Count])-AVG([Holidays])

Step Seven:  Create Days Missed to Date calc

Days Missed To Date

[Actual Class Length to Date]-[Actual Class Length to Date]*MAX([Student Percentage]/100)

Step Eight:  Double-Click on each of your new measure to add to the view

End result should look like this:

I've attached a packaged workbook for reference.

Thank you,

John

• ###### 2. Re: Number of missed classes with just a Start Date and Percentage

Thanks! I will give you an update after I try it.

• ###### 3. Re: Number of missed classes with just a Start Date and Percentage

Hi John,

Sorry it took me so long to get back to you. It worked great. There would have been no way that I could have figured this out.

Thank you so much!!!

• ###### 4. Re: Number of missed classes with just a Start Date and Percentage

You're welcome!   Glad it worked out!