11 Replies Latest reply on Sep 26, 2014 12:48 PM by Matt Lutton


    Clinton Bruns

      Please help,


        This is my last hope before I pull my hair out.  I apologize in advance for how complicated this is, and I appreciate whoever takes this on with me. I work on a project where employees go through three "transition periods" right when they start the job.  The first period is the first four weeks on the job, the second is the fifth week, and the third is the sixth week.  In any one of those periods they have to get 90% or better in the metric I have in the file to be able to "pass".  However they only have to pass one.  If they pass the first period, they are considered a pass regardless of the next two, and so on.  What I want to display is who has passed a transition period for each week, what week it was, and what transition period it was.  The issue I am having is when I try to add in some logic to not give a result if the period is not completed.  So even if you are over 90% in the period, if the period hasn't been completed, you still haven't "passed".  In the attached I have a calculation that gives me all of these individually: The most recent period completed ("Period Completed"), and the first period that was passed, if any, labeled "Trn Period Passed".  However, when I combine them it gives multiple results for each person.  It works on an individual level, but not when all names are showing.  Please help!

        • 1. Re: Argghh
          John Munoz

          Hi Clinton,


          Interesting problem. I will try to have a look at your twbx file tonight and see what I can do...just want to be sure you haven't cracked this nut though in the 8 days since you posted it...have you?

          • 2. Re: Argghh
            Matt Lutton

            I did take a look when this was posted, but wasn't able to devote much time to it either;  I do have advice on the Title of the thread--that is what shows up in the Forum search results, list of Discussions, etc. so you may want to change it to something that relates the overall idea/problem you're struggling with.  That can be difficult to do, as well, but I think it will help you garner more attention to the post.

            • 3. Re: Argghh
              John Munoz

              Good point Matt Lutton! More specific Title and tags would help.


              On the other hand, I've felt that same frustration over the years using Tab myself. And that's why I read Clinton's post.


              Off to fix a leaky roof before the rain hits, then back to this problem.

              • 4. Re: Argghh
                Matt Lutton

                Oh definitely--it drew me in when he originally posted it.  Sometimes I go to my little restroom by my desk, and notice my hair is sticking up all over the place.  I'll attribute those moments as ARGGGGGGGGHHHHHHHH moments, as well!


                Clinton, I may not know the solution to your problem, but I'd love to see you find one as well so I'm keeping my eyes on this and will look at your workbook again when I have more time to focus.  You've posted an example workbook and described your end goals, so I certainly appreciate that as a Forum user!  I'd say at this point, changing the title of the thread couldn't hurt


                I will only add that it is sometimes very difficult to answer questions like this on the Forums, simply because of the number of fields, calcs, sheets, etc. in the workbook.  If there is any way to trim down the workbook, name your sheets so we know what they were used for, and add captions to explain the sheets you've built, the calcs you're using, etc., it may go a long way toward getting your final solution as well. For good examples of workbooks that use a lot of captions, etc. to explain what's going on, take a look at Jonathan Drummey's blog--the conditional formatting workbook he posted here is a great example (although he is showing mostly solutions, not problems): Older But Still Useful – Conditional Formatting | Drawing with Numbers

                • 5. Re: Argghh
                  John Munoz

                  Matt Lutton, if you want to team up on this one, let me know and I'll be happy to set up a WebEx. No pressure though, I know there are many other deserving unanswered forum questions out there looking for a hand.

                  • 6. Re: Argghh
                    Matt Lutton

                    Reach out via the email in my profile if you want to discuss this a bit. 

                    • 7. Re: Argghh
                      Clinton Bruns

                      Thank you for your response, and everyone else as well.  No I haven’t really gotten this to where I want it.  When I put on the “Week Passed” measure it creates two records for each employee.  One of these is usually null, which I am not interested in so I can just filter it out.  However, sometimes the duplicated item is a field that I want to keep, so I cannot just filter it.  Also, I apologize for the name of the thread.

                      • 8. Re: Argghh
                        Jim Wahl

                        What if you change the Week Passed field to handle Null values where appropriate -- i.e., when the user hasn't passed any time periods -- by adding an ELSE "" to the end of the case statement.


                        And now you can filter the "legitimate" nulls from Weeks Passed.


                        • 9. Re: Argghh
                          Matt Lutton

                          I do not know how you get to the Crux of an issue in examples like this so quickly, Jim!  I think I get overwhelmed by all the "stuff" on the screen, sometimes and I lose focus of what the "problem" is.  I'm very impressed, I can tell you that much. 

                          • 10. Re: Argghh
                            Jim Wahl

                            Thanks Matt. I think I got a lot faster after I forced myself to slow down.


                            I've wasted a lot of time by not building table calcs one function at a time before combining everything into a single calculated field.

                            • 11. Re: Argghh
                              Matt Lutton

                              I could definitely benefit from slowing down; I see the unanswered questions on the Forums and feel I must try and save everyone, and I miss a lot of details by trying to do too much at once.


                              I'm really happy to see so many of the folks I consider to be gurus posting more often.  Cheers.