1 2 Previous Next 23 Replies Latest reply on Sep 14, 2018 10:36 AM by Joe Oppelt

    Days between two periods with overlapping data

    tarang.patel.3

      Hi all,

       

      I am trying to calculate the days a product is used in a month. The "should be" column is the value I'm trying to arrive at. My objective is calculate a "usage" when a users uses a product on a particular day. The data might have overlapping instances and therefore should not be calculate. I've attached the data file.

       

      Thanks for your help!

       

       

        

      USER IDMODELSTART DATEEND DATESHOULD BE

           

      111234Red9/2/20189/12/201811
      111234Red9/7/20189/9/20180
      111234Red9/7/20189/17/20185
      111234Red9/17/20189/20/20182
      111234Red9/21/20189/21/20181
        • 1. Re: Days between two periods with overlapping data
          Joe Oppelt

          See attached.  I saved it as V10.2.

           

          I had an application just like this.  I needed to find the gaps in continuous days.  (So the gap between the last two rows for each user would have been 1 in your data.)

           

          The key is to keep a table calc that remembers what the furthest date is as we walk down the table.  And to make that work I use a function called PREVIOUS_VALUE.

           

          Not that when I have multiple rows that start on the same day (such as the two that start on 9/5) I only look at the one that has the longest end date.  The ones with shorter end dates will be zero by definition if you have to assign values to them.  But I think your exercise is really trying to identify those rows that actually get non-zero numbers anyway.

           

          Are you familiar with doing settings on table calcs?  That's important for the running end date calc, for index, and for [days].

           

          Also I use LODs to make some of the calcs.  (It turns out that I don't need [Min Start Date] at all for your exercise, so you can cut that one out.

           

          I also realize that I left out [Model].  You will need to insert [Model] into the LODs, and also into the table calcs.

           

          If this is what you are looking for, and if you need help adapting this to your actual application, just ping back and I can help.

          1 of 1 people found this helpful
          • 2. Re: Days between two periods with overlapping data
            tarang.patel.3

            Hi Joe,

             

            This is amazing! I'm still working through the logic as to how you came up with this magic, but I had one question in the meantime.

             

            Which dimensions/measures does [Model] need to be added to the LODs? It seems that if I pull [Model] into the rows before user id it still works.

            • 3. Re: Days between two periods with overlapping data
              Joe Oppelt

              LODs aren't picky about dimension order.  (Sometimes table calc settings are picky about that!)  I would insert [Model] after [UserID].  And in the calc where I also use [Start Date], I would insert it between [UserID] and [start Date].

              1 of 1 people found this helpful
              • 4. Re: Days between two periods with overlapping data
                tarang.patel.3

                How'd you manage to get Index() to read 1,2,3,4 I can only get it to go 1,1,1,1?

                • 5. Re: Days between two periods with overlapping data
                  Joe Oppelt

                  Right click on the index pill and select "edit table calc".  You'll get this pop-up box:

                   

                   

                  I selected specific dimensions (I could have added Min Start Date as well).  Most importantly, [User ID] is at the top of the list.  (You can drag them around and change the order.)  Then I selected "Restarting every" and have it restart for each User ID.  I forgot that I had set "at the level" to [Start Date].  That one doesn't matter.  You can leave it as "Deepest" (which is the default.)

                   

                  In fact, all the table calcs are set like this.  If you add [Model] to the mix, you would need to select that too.  You would want it positioned right under User ID, and you probably want to restart every [Model] instead of every UserID.

                  1 of 1 people found this helpful
                  • 6. Re: Days between two periods with overlapping data
                    tarang.patel.3

                    Hi Joe,

                     

                    This seems to work great for the most part.

                     

                    I'm running into a problem when I translate this logic into my actual data.

                     

                    Problem: All the "Running End Date" equals "Max End Date Per Start Date"

                    It seems to be reading "Index" as a value of 1 for all rows even though the table shows otherwise. However, I cannot double check this because when I try to "Export All Data" the "Index" column does not appear.

                     

                    See problem below. Row 22 encompasses row 21, but still shows days of use as 1. Any clue?

                     

                    • 7. Re: Days between two periods with overlapping data
                      Joe Oppelt

                      In this screen shot I'm editing the table calc settings for [Running End Date].

                       

                       

                      Notice that there is a pull-down for Nested Calculations.  [index] is nested in [Running End Date].  Look at what the settings for [index] are under the [Running End Date] edit box.  This tells Tableau how to handle [index] within [Running End Date].  Tableau can't assume that the way it is used elsewhere on the sheet should be used within the nesting of this calc.  So you need to make sure you specify it here too.  (And yes, I've had situations where I use something like [index] with different settings all within the same sheet.  Sometimes I want it counting down the sheet.  Sometimes across.  So it can happen, and that's why Tableau behaves this way.)


                      In my example, [index] is set the same as [Running End Date] under the [Running End Date] table calc settings.

                      1 of 1 people found this helpful
                      • 8. Re: Days between two periods with overlapping data
                        tarang.patel.3

                        I'm assuming this is the same for the "Days" measure?

                        • 9. Re: Days between two periods with overlapping data
                          Joe Oppelt

                          Yes.  and, in fact, notice that there are THREE nested calcs in that one.  All set the same in this example.

                          1 of 1 people found this helpful
                          • 10. Re: Days between two periods with overlapping data
                            tarang.patel.3

                            Would you recommend setting up all the nested calculations in terms of order of the measures, at the level, and restarting every? Should I also, include Product Line here?

                             

                            Thanks again for your super quick responses. I've been struggling on this for a while and you've been a big help.

                            • 11. Re: Days between two periods with overlapping data
                              Joe Oppelt

                              For the order of dimensions in the "edit table calc" list, I would place user-id first.  Then product line.  and you would probably need to restart every product-line.  The order of the other dimensions probably won't matter.

                               

                              You probably won't need to mess with "at the level of".

                               

                              What will really matter is that you get index cycling the way you want it to go.  You want 1-through-N within each product line, within each user-id.  If you get that, you'll probably end up setting all the other calcs the same way.  I use index like this a lot.  If I see the sequence I want, I know I have the ordering correct, and everything falls into place.

                              1 of 1 people found this helpful
                              • 12. Re: Days between two periods with overlapping data
                                tarang.patel.3

                                Thanks Joe.

                                 

                                Is there a way to get a number of days used summary by month? For example see the last two rows below. This data now bleeds into a second month. I attached a new Excel file.

                                 

                                I suppose one of two options would work. Not sure which one is the best route.

                                1. Having the 'days' measure calculate only up to the last day in the month.

                                or

                                2. A separate summary table by month

                                 

                                 

                                Also, it seem like 4th row in the table below or row 8 in the Excel file isn't working.

                                    

                                 

                                 

                                Can't thank you enough!

                                • 13. Re: Days between two periods with overlapping data
                                  Joe Oppelt

                                  I swapped in the new data set.


                                  Before I do anything else, I need to let you know that I had a bug in the [Days] calculation.

                                   

                                  With the new data, the last row for UserID 423423 was coming up with 96 days.  It was doing a datediff between 7/21/2017 and 10/25/2017, where it should really be doing it between 9/20 and 10/25 for a count of 36.  Here is the change I made:

                                   

                                  if [index]=1 then datediff('day', attr([Start Date]),attr([Max end date per start date]))+1  // first row

                                  else

                                    if attr([Start Date]) <= lookup([Running End Date], -1) // this row does not start beyond the running end date
                                      then
                                        if  attr([Max end date per start date]) > lookup([Running End Date], -1) // new end date is beyond old
                                          then
                                          datediff( 'day', lookup([Running End Date], -1) , attr([Max end date per start date]))
                                          else 0
                                        END
                                      else  //  this row starts beyond the running end date
                                          datediff( 'day', attr([Start Date]),attr([Max end date per start date]))+1
                                      end
                                  end

                                   

                                  I want to know if my start date is beyond the running end date of the prior row.  Not the running end date of the current row.  So I have that straightened out in the new workbook attached.

                                   

                                  Now, having said that, I need to understand what you're really looking for here.  And the last row of 423423 is a great example to look at.  We have 36 days here.  Some in September and some in October.  We can separate those out easily enough because there are only two months to deal with, and we have dates from both those months in this row.  But supposed that start date had been August 20.  We would have 67 days, and we can know that August has 31 days (so 12 belong to August, and 30 to September, and 25 to October.)  But we can't make a mark for September for this row.   The row wouldn't have a September date in it, and therefore we can't make a place in the table to store September's 30 days to sum up with the other rows to get an overall September total.

                                   

                                  If you just want to sum up total days by start date (so the full 36 days in your current data would be credited to September for that last row of 423423), then your solution will be easy.  But if you want to break out 11 days to September and 25 to October, we're going to need to reshape this data.

                                  1 of 1 people found this helpful
                                  • 14. Re: Days between two periods with overlapping data
                                    tarang.patel.3

                                    Hi Joe,

                                     

                                    I think explaining my end goal will help arrive at the best approach.

                                     

                                    I'm looking for average monthly usage by User ID by product. The calculation for that would be sum total days used / # of unique months.

                                     

                                    Example: User ID 423423 uses Product Red for an average of 21.75 days/month.

                                     

                                    21.75 days/month = 87 days of use/ 4 unique months product was used.

                                     

                                    I think this would mean that we would need to show the 'days' by each individual month. August, September, October.

                                     

                                    Does that make sense?

                                    1 2 Previous Next