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

    Level of Detail IF Statement Problem

    Ashley Dettlaff

      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
          Deepak Rai

          Ok, Couple of Suggestions

          1. Join your data
          2. Create Initial View with that
          3. Let's know then where are you Stuck
          4. Paste a workbook to Help you.

          Thanks

          Deepak

          • 2. Re: Level of Detail IF Statement Problem
            Ashley Dettlaff

            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