2 Replies Latest reply on Jul 16, 2018 7:39 AM by Ashley Dettlaff

# Level of Detail IF Statement Problem

Scenario: several sheets of mail are scanned on a given day, each receiving a time stamp and a unique job id. I want to compare the scan time to another set time, but that sheet only has one time per each date and no job ID to match to.

Logic I would like to use:

if [scan time] < [finished scan] then [elapsed hours]+24

(I converted the times out of a 06:23 and into a 6.383 format in order to compare integers)

The Problem--> the data is across two sheets so there will need to be an aggregate function, but I cannot use ATTR because I will just get *. I cannot SUM([scan time]) for the particular date in question, because then the total will certainly be too large.

End Goal: once the 24 hour correction is added to the appropriate rows in sheet one, then I will count how many jobs have elapsed hours>48 and calculate COUNT([jobs over 48])/total jobs. So all that needs to be displayed is that percentage on each date

I am not attaching a workbook because I cannot display anything until I get this comparing values problem figured out. Attached is sample data in an excel sheet.

If anyone can help get to a point where the elapsed hours are correct in tableau it would be much appreciated!

• ###### 1. Re: Level of Detail IF Statement Problem

Ok, Couple of Suggestions

2. Create Initial View with that
3. Let's know then where are you Stuck

Thanks

Deepak

• ###### 2. Re: Level of Detail IF Statement Problem

In this workbook I can get to the point of adding 24 hours where needed if Job ID is on the column. However, I need everything based on date. So basically I need to perform calculations using each individual row/job ID but display it based on date when it comes to using count(missed)/count(total) for that day. In the attached workbook missed jobs and total jobs are coming up as the same so the percentage is just 0.

Note in the real data there are many more lines and I cannot have tableau even use Job ID as a column entry because there are too many unique options. In this smaller example is can be seen though.

End Goal: once the 24 hour correction is added to the appropriate rows in sheet one, then I would like to count how many jobs have elapsed hours>48 and calculate COUNT([jobs over 48])/total jobs for a specified date