1 2 Previous Next 19 Replies Latest reply on Aug 23, 2016 10:36 AM by Joe Oppelt

    Hi there!

    Tracy Orwig

      I am in need to find 7 days + that are in sequential order and then only show the data for that 7th day + in that column.  I have tried suggestions that I have read on the forums but  have yet found exactly what I need. 

      so in this workbook Sheet 8 gives you Month/Week/Weekday and you can see those who have more than 7 days with data. 

        • 2. Re: Hi there!
          Joe Oppelt

          What I did in there:

           

           

          Sheet 9 is a copy of your sheet 8.  I created a calc that keeps track of what I have in the current cell, and what was in the prior cell.  The PRIOR_VALUE is a cool function that lets you add to the previous value.  So we get a sort of running count of cells that have values, and resets to 0 of the value is null.

           

          Sheet 10 is a copy of 9.  I created a calc that displays the value only if [Value Count] is greater than or equal to  7.

           

          Sheet 11 is a copy of 10.  I created one more calc to determine if I have the last value in a string of counts, and if it is bigger than 7.  (Or if I am at the end of the row and I am bigger than 6.)  If so, then display it.

           

           

          If I understand your problem statement, it's this last calc that you really want to get to.

          • 3. Re: Hi there!
            Tracy Orwig

            Good morning Joe!

             

            Thank you so much for your quick response!  I started fiddling with it this morning, I believe this will work for what I am trying to find.  I will probably need to make a Fixed formula so that when I get rid of those who do not show over 7 that it doesn’t change the Value Count.  I noticed when I put it into a filter it adjusted some of the rows.

            I also have to somehow work this into my other dashboard so that those hours are removed from a few other situations.  It’s an interesting project where we want to see hours by day due to the different regulations in place for our partner in Puerto Rico.  They have rules set in place for overtime that are unique so in order to get our costs in line we want to ensure we are not seeing a ton of overtime that could have been avoided.  Hopefully this will help manage that team to be more profitable!

             

            Thanks again!

             

            Tracy Orwig

            Business Analyst

            Call Center Analytics

            ext 5288

            • 4. Re: Hi there!
              Joe Oppelt

              You don't need to filter anything.

               

              See Sheet 12.  It's a copy of 11.  I just moved the intermediate calcs from TEXT to the DATA shelf.  Now the only thing that displays are the ones you want to see.

              • 5. Re: Hi there!
                Tracy Orwig

                I need to be able to roll it up to the Month/Phone Center level.  Once I remove these fields from rows and columns I just see the Displayed number rolled up but not in their actual month.  Is there a way to have those hours show in that format?  Thanks again for your help Joe!

                 

                Tracy Orwig

                Business Analyst

                • 6. Re: Hi there!
                  Joe Oppelt

                  And if you do want to chop out those [Communicator] rows that have no data, see sheet 13.

                   

                  I took the final calc and replicated it on filters.  Selected for non-null data, and now only those rows that have something get displayed.

                   

                  This filter doesn't mess with the index counts because it's a table calc.  Using a table calc as a filter doesn't discard rows from the underlying table like a quick filter does.  It just discards rows from what is displayed.

                  • 7. Re: Hi there!
                    Tracy Orwig

                    Thanks Joe,

                     

                    I would need something more like sheet 11 where I can get a sum of those that had 7 or more days rolled up to the month view.  On sheet 13 it is just showing the LAST displayed number.

                    Tracy Orwig

                    Business Analyst

                    • 8. Re: Hi there!
                      Joe Oppelt

                      You can roll  up these numbers with another table calc.  In the attached (sheet 14) I am doing a window_sum of the final numbers we had in sheet 13.  In this case I am adding up all the values for each day for each Phone center.  We can tell that window sum to address the dimensions in all sorts of ways, so we could have a total by week, by month, etc.

                       

                      Keep in mind, Sheet 14 is only an interim step.  Depending on what you want your final viz to look like, we will move things around accordingly.  And as you can see, you get one displayed value of the roll-up calc for each occurrence of a value within a Phone Center.  So, for example, look at Friday, June, Week 27.  For Center 1 we have several outputs of 111.9, and for center 2 we have several for 43.3.  If you want this rolled up by center without displaying the individual Communicators, we'll still have a bunch of individual occurrences of 111.9 shoved into a single cell,  We can tell Tableau to spit out only one.  But we'll have to tailor that to the viz you actually want to see.

                      • 9. Re: Hi there!
                        Joe Oppelt

                        Tracy Orwig wrote:

                         

                        Thanks Joe,

                         

                        I would need something more like sheet 11 where I can get a sum of those that had 7 or more days rolled up to the month view.  On sheet 13 it is just showing the LAST displayed number.

                        Tracy Orwig

                        Business Analyst

                        And that's even something different.

                         

                        So on Sheet 11, scroll down to "Ra9" in Center 1.  For June, week 27, are you saying you want to add up 8.0+10.98+11.0 ?  I'm assuming that for all the singleton values, it's just the value itself, but for strings of values like this, you want the whole string added until we finally reset back to zero.

                         

                        We can do that too, but I want to make sure I'm aiming for the right thing.

                        • 10. Re: Hi there!
                          Joe Oppelt

                          And what do you mean by "roll it up to the month/Phone Center level" in the context of adding up these strings?

                           

                          And what happens if a string of values crosses from the last day of one month to the first day of the next?

                          • 11. Re: Hi there!
                            Tracy Orwig

                            I need to sum up for those that are showing greater than 7 at the Phone Center Level.  IF a day falls into a different month out of that string it can reside in the true month.

                            So for example:

                            Week view

                             

                             

                             

                             

                             

                            January

                             

                             

                             

                            February

                             

                            comm

                             

                            week

                             

                            1

                             

                            2

                             

                            3

                             

                             

                             

                            day

                             

                            7

                             

                            8

                             

                            9

                             

                            abc

                             

                             

                             

                            10

                             

                            10

                             

                            5

                             

                            xyz

                             

                             

                             

                            20

                             

                            25

                             

                            5

                             

                            Month view

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

                             

                            phone center

                             

                             

                             

                            January

                             

                            February

                             

                            TOTAL

                             

                            CS 1

                             

                             

                             

                            65

                             

                            10

                             

                            75

                             

                             

                             

                            Tracy Orwig

                            Business Analyst

                            Call Center Analytics

                            ext 5288

                            • 12. Re: Hi there!
                              Joe Oppelt

                              Can you plug that into an excel sheet or something with a better format?

                               

                              I'm assuming those are numbers from Sheet 11.  If not, can you use Sheet 11 numbers so I have a concrete example to follow?

                               

                               

                              PS:  I'm going to have to pick this up tomorrow. I have commitments for the rest of today already.

                              • 13. Re: Hi there!
                                Tracy Orwig

                                Sorry about that, I just made up a mock example.  Thanks!

                                 

                                Tracy Orwig

                                Business Analyst

                                Call Center Analytics

                                ext 5288

                                • 14. Re: Hi there!
                                  Joe Oppelt

                                  Tracy -- Back looking at this.

                                   

                                  The mock-up doesn't help me enough.

                                   

                                  On sheet 11 in the example, look at Comm2, Communicator="SA4", for weeks 31 and 32.  You can see that there is a string of values that crosses the week boundary, and the month boundary.  For those three numbers, what values would you expect to see by week?  And by month?

                                  1 2 Previous Next