4 Replies Latest reply on May 16, 2016 11:18 AM by Stephanie Chang

    Excluding and Associating Data in Tableau

    Stephanie Chang

      Hello,

       

      I am wondering if Tableau has the ability to recognize certain data and either exclude them or include them into further calculations? For example:

       

      • I am part of a department that needs to measure AVG Response Times of certain units. However, we only want to include specific units, not all the responding units. If I upload a dataset, can I somehow program Tableau to exclude certain items in its calculation automatically?
      • Each unit has a certain number associated with it, as well as a time. For Example, Unit A - 4, Unit B - 2, etc. Can I input this into Tableau somehow and get Tableau to run aggregates on the Units, and once the Units and the number associated aggregate to 14, to then trigger Tableau to track the time of the Last Unit?

       

      I can upload a dataset if it helps to make more sense of my question. Thank you!

        • 1. Re: Excluding and Associating Data in Tableau
          khalid norat

          A dataset would be helpful

          also maybe a and example of some out puts and what it should look like either in excel or screen shots would be ideal

          • 2. Re: Excluding and Associating Data in Tableau
            Tharashasank Davuluru

            Hi,

             

            Attach the data set and O/p how you want . it will be easy .

            • 3. Re: Excluding and Associating Data in Tableau
              Stephanie Chang

              Hello, thank you for your help!

               

              This is quite a complicated request, and  Most of this work can be done in Excel so I suppose I can clean up the data before I submit into Tableau. But I was hoping for an easy workaround and to just be able to upload into Tableau on a monthly basis once the formula is created, without having to do the legwork in Excel. But if necessary, I can also clean up the Excel data a bit before I submit in order to make the process easier.

               

              Attached is the dataset I’m working with. To quickly explain, IN (Column A) shows the Incident Number, and for each Incident there will be multiple responding Units, indicated in  (Column C). So for each unique IN incident number, I would like Tableau to be able to recognize a couple things:

               

              ·         First Unit that was OnScene per Incident Number (Column N shows OnScene time in the format of date and time)

              However, this is a little more complicated, as “First Unit” requirements are only met when we have a minimum of 4 personnel onscene, and there is a specific number of personnel attached to each Unit. So I would need Tableau to look at each unique Incident, pick up on the first Unit (Column C) that was OnScene (Column N), and then see if it meets the requirement of 4 personnel. If it does not, then move on to the next Unit attached to that Incident, and look at how many personnel are attached to that Unit, and If added together it meets 4 personnel (and so on if it does not). Then once it does meet the “First Unit” requirement, Tableau can pick up on that OnScene time (Column N), and use that data.

               

              I would like to track Onscene time times by when the Incidents meet these thresholds:

               

              ·         First Unit: 4 personnel

               

              ·         ERF1: 12 personnel

               

              ·         ERF2: 20 personnel

               

              And of course, not every incident will meet ERF1 and ERF2, but they should all meet First Unit.

               

              In Column C, I also get data for a lot of Units we don’t include in our calculations, so I would like Tableau to only recognize these Units indicated below. And beyond that, the associated number of personnel for each Unit type is in red:

               

              ·         BC3, BC31 - 1

               

              ·         BIK31, BIK32, BIK33, BIK34, BIK35, BIK36, BIK37, BIK38, BIK39 - 2

               

              ·         DEP 3 - 1

               

              ·         E31, E32, E33, E34, E36, E37, E38, E39 - 4

               

              ·         EMSCH - 1

               

              ·         RA31, RA32, RA33, RA34, RA36, RA731, RA732, RA733, RA734, RA736 - 2

               

              ·         T31, T32 - 4

               

              ·         I3 - 1

               

              ·         PR 31 - 1

               

              ·         RC32 - 2

               

              ·         WT37, WT3 - 2

               

               

              These are the information I would like to capture once the thresholds are met for First Due, ERF1, and ERF2:

               

               

              ·         Dispatch (Column K), Enroute (Column L), OnScene (Column N)

               

              Thanks again for your help, very much appreciated!

              Stephanie

              • 4. Re: Excluding and Associating Data in Tableau
                Stephanie Chang

                Hello, thank you for your help!

                 

                This is quite a complicated request, and  Most of this work can be done in Excel so I suppose I can clean up the data before I submit into Tableau. But I was hoping for an easy workaround and to just be able to upload into Tableau on a monthly basis once the formula is created, without having to do the legwork in Excel. But if necessary, I can also clean up the Excel data a bit before I submit in order to make the process easier.

                 

                Attached is the dataset I’m working with. To quickly explain, IN (Column A) shows the Incident Number, and for each Incident there will be multiple responding Units, indicated in  (Column C). So for each unique IN incident number, I would like Tableau to be able to recognize a couple things:

                 

                ·         First Unit that was OnScene per Incident Number (Column N shows OnScene time in the format of date and time)

                However, this is a little more complicated, as “First Unit” requirements are only met when we have a minimum of 4 personnel onscene, and there is a specific number of personnel attached to each Unit. So I would need Tableau to look at each unique Incident, pick up on the first Unit (Column C) that was OnScene (Column N), and then see if it meets the requirement of 4 personnel. If it does not, then move on to the next Unit attached to that Incident, and look at how many personnel are attached to that Unit, and If added together it meets 4 personnel (and so on if it does not). Then once it does meet the “First Unit” requirement, Tableau can pick up on that OnScene time (Column N), and use that data.

                 

                I would like to track Onscene time times by when the Incidents meet these thresholds:

                 

                ·         First Unit: 4 personnel

                 

                ·         ERF1: 12 personnel

                 

                ·         ERF2: 20 personnel

                 

                And of course, not every incident will meet ERF1 and ERF2, but they should all meet First Unit.

                 

                In Column C, I also get data for a lot of Units we don’t include in our calculations, so I would like Tableau to only recognize these Units indicated below. And beyond that, the associated number of personnel for each Unit type is in red:

                 

                ·         BC3, BC31 - 1

                 

                ·         BIK31, BIK32, BIK33, BIK34, BIK35, BIK36, BIK37, BIK38, BIK39 - 2

                 

                ·         DEP 3 - 1

                 

                ·         E31, E32, E33, E34, E36, E37, E38, E39 - 4

                 

                ·         EMSCH - 1

                 

                ·         RA31, RA32, RA33, RA34, RA36, RA731, RA732, RA733, RA734, RA736 - 2

                 

                ·         T31, T32 - 4

                 

                ·         I3 - 1

                 

                ·         PR 31 - 1

                 

                ·         RC32 - 2

                 

                ·         WT37, WT3 - 2

                 

                 

                These are the information I would like to capture once the thresholds are met for First Due, ERF1, and ERF2:

                 

                 

                ·         Dispatch (Column K), Enroute (Column L), OnScene (Column N)

                 

                Thanks again for your help, very much appreciated!

                Stephanie