12 Replies Latest reply on May 15, 2019 2:56 PM by Shafi Hadimani

    List of items not completed

    Shafi Hadimani

      Hello All,

         I have a data set in which there  is a list of trainings which the engineers have to do.

      All, the engineers have not done all the trainings. For example, if there are 10 trainings then Engineer A has done only 3 and Engineer B has done 6.

      I would like to know which are the 7 trainings which Engineer A has not done, similarly the 4 trainings which has not be done by Engineer B.

      Attached is the excel sheet

        • 1. Re: List of items not completed
          Joe Oppelt

          Make a fixed LOD like this:

           

          { FIXED [Engineer ID], [Training ID]  : SUM(if ISNULL([Complete] then 0 else 1 END) }

           

          You'll get a 0 or 1 for each training course for each engineer.  0 = did-not-do.

           

          You could make that a dimension if you wanted.

           

          You can filter on that, you can sum it (to tell you how many courses any engineer has done), etc.

          • 2. Re: List of items not completed
            Joe Oppelt

            Oh wait...  Looking at your data, you already have a [Completed Yes/No] column.  That's all you need.

            • 3. Re: List of items not completed
              Shafi Hadimani

              Hi Joe,

              Completed Yes/No does not talk about if the engineer has completed the training or not and hence we cant use that.

              Coming to the fixed LOD which you suggested,

              I did the following

              { FIXED [First], [Content Title]  : SUM(if ISNULL([Complete]) then 0 else 1 END) }

              First = Name of the engineer

              Content Title = name of the training

              I was not able to figure out what to put under Complete. In my data complete is a date field.

              Attached is the workbook..  Worksheet name "List of  trainings"

              Calculated field name: "List of Trainings"

               

               

              • 4. Re: List of items not completed
                Shafi Hadimani

                Please note even if complete is blank, it still means the training is completed. That is, if the name of the agent is in front of the title of the training its assumed that the training is completed..

                • 5. Re: List of items not completed
                  Joe Oppelt

                  I just assumed that [Complete] was the date the engineer finished the course.

                   

                  What indicates that an engineer finished a course?

                   

                  Or what indicates that a course an engineer is supposed to complete has not been completed?

                  • 6. Re: List of items not completed
                    Joe Oppelt

                    Shafi Hadimani wrote:

                     

                    ... That is, if the name of the agent is in front of the title of the training its assumed that the training is completed..

                    What does that mean?  I can't see anything like that in the data.

                    • 7. Re: List of items not completed
                      Joe Oppelt

                      Also, I'm noticing that some engineers have multiple rows for the same training.  Look at [First] = "Ana" 

                       

                      Is that a legitimate condition in your full set of data?

                      • 8. Re: List of items not completed
                        Shafi Hadimani

                        Okay. Let me exaplin the data.

                        There is a training website on which the trainers upload the trainings. Engineers go to this website and as soon as they click on the title of the training it is registered as this course was taken by the engineer. Now in our website there is no definitive way of saying if the engineer has completed the training after he has started the training.

                        So, what we do is, after downloading the data from the website we see who's name is infront of which training.

                        For example "Training 1" is taken by Engineer A, B and C

                        "Training 2" is taken by Engineer B and C.

                        We know this because when we pull the data, we will have engineers name in front of training title (apart from the other details which you see in the excel sheet which I have attached previously)

                        Training 1  Engineer A

                        Training 1  Engineer B

                        Training 1  Engineer C

                        Training 2  Engineer B

                        Training 2  Engineer C

                         

                         

                        Now there will be instances when Engineer A, would have clicked on Training 1 twice. Hence, we will find instances when 1 training would have been done by a single engineer multiple times.

                         

                         

                        To get the unique count of the trainings which each engineer did I applied the formula countd(content title). This gave me the unique count (no duplicates) of trainings which each engineer did.

                         

                         

                        Now, I want to know which are the trainings which they have not done. In the above example I would like to show that Engineer A  has not done Training 1.

                        • 9. Re: List of items not completed
                          Joe Oppelt

                          In your sample excel file there are 552 unique courses.  Engineer "Aas" has rows for 5 of them.  Are you suggesting that he still has 547 more to do, and you want the list of those 547 for him?  And the same for Abdul (but a different 5 are taken.)  And Abhijit has done 24, so you want the remaining 528?  Etc.

                          • 10. Re: List of items not completed
                            Shafi Hadimani

                            Exactly. You got that right

                            • 11. Re: List of items not completed
                              Joe Oppelt

                              You'll need to ETL the data one way or another so that for every Title, you have a row of every engineer listed.  You have 522 titles, and 439 engineers, so you'll end up with 522x439 rows.  Combinations that do not currently have a row would have all NULL values for the rest of the columns.


                              Tableau doesn't report on what is not there.  Only on what is there.

                              1 of 1 people found this helpful
                              • 12. Re: List of items not completed
                                Shafi Hadimani

                                Thank you Joe, for helping..