1 2 Previous Next 18 Replies Latest reply on Aug 24, 2016 12:53 AM by Prasenjeet Acharjee

    finding number of days between two dates

    SUK Rudge

      i want to create a G CHART for no of days between each fall and fall.

      How to calculate number of days between two dates in the same column.

       

      For Ex if one fall happen on 1st july 2012,second on 2nd sep 2012 and 3rd happen 15nov 2012 how can i calculate no of days between 1st july 2012 and 2nd sep 2012 , no of days between 2nd sep 2012 and 15 nov 2012.

       

      All these dates reside in the same column field.

      pls help me with this query.

        • 1. Re: finding number of days between two dates
          Joshua Milligan

          SUK,

           

          I think the answer is a calculated field using DATEDIFF and a lookup table calculation.

           

          The calculation might look like this:

           

          DateDiff('day', Lookup(ATTR(Date), 0), Lookup(ATTR([Date]), 1))

           

          and the results are attached.  Hopefully that gives you a good starting place.  If you want to attach your data that might help too.

           

          DateDiff with Lookup.twbx.png

           

          Joshua

          • 2. Re: finding number of days between two dates
            Jonathan Drummey

            Hi Joshua,

             

            For performance, the first LOOKUP() is not necessary - LOOKUP(ATTR([Date]),0) will return the same results as ATTR([Date]) - in this example at least. If the dates don't include times, I suspect that LOOKUP(ATTR([Date]),1)-ATTR([Date]) might be even faster, since it's just subtracting the internal decimal days representation of dates.

             

            Jonathan

            • 3. Re: finding number of days between two dates
              Joshua Milligan

              Jonathan,

               

              Thanks!  What's an example of a case where ATTR([Date]) would not be the same as LOOKUP(ATTR([Date]), 0)?

               

              Joshua

              • 4. Re: finding number of days between two dates
                Jonathan Drummey

                Ok, that was a productive bit of time... The comment I made was a hedge, because I'm still learning the ins and outs of date padding and domain completion, and I wasn't sure what ATTR() did in all of those situations. I just set up a bunch of tests of date padding and domain completion and didn't see any cases where the LOOKUP(ATTR(),0) returned a different value than ATTR(). Thanks for asking!

                • 5. Re: finding number of days between two dates
                  Joshua Milligan

                  Jonathan,

                   

                  So, what are some good resources for learning about domain padding in Tableau?  I had a brief discussion with Joe Mako at TCC in which he asked me what I knew about it and I just stared blankly back at him.  I still don't have a good grasp of the concept and I haven't found much on the subject.

                   

                  Joshua

                  • 6. Re: finding number of days between two dates
                    Jonathan Drummey

                    1) Joe Mako

                    2) [silence...]

                     

                    A bit more seriously, my own knowledge is coming along and Richard Leeke also knows a fair bit, and it's very much practical knowledge found by trial, error (lots of error), and experimentation. Tableau staff like Ross Bunker and Jock Mackinlay know a lot because they designed and built it, and they're busy developing new features so we don't like to bother them.

                     

                    Overall, Tableau calls this process "densification." Domain padding is a form of densification that Tableau uses with dates & bins, for example when you turn on the Show Missing Values option. Domain completion is what Tableau does when you have discrete pills on Rows and Columns with sparse data, Tableau pads out the data so there is a pane/cell for every combination of discrete values. Where this gets complicated is that domain completion and padding can look like each other, and there are times based on pill type, location, and mark type that densification can go away and/or come back. These factors are not documented and the set of factors and outcomes is still being discovered. Just last week Joe and I found a situation where Tableau started doing domain padding when neither of us had expected it to happen. We're trying to put together some more details on this, and it's a slow process.

                     

                    As for why there isn't more documentation on this, I think it's kind of a scaffolding issue, that there haven't been enough people at Tableau or end users who have found use cases where densification is an issue (or not), grasp how Tableau works in this area, what the opportunities are, and the limitations. And I'm guessing that Tableau hasn't really wanted to document it because there are so many complexities, that they'd like to clean up the underlying functionality and make it adhere to more of the Tableau philosophy.

                     

                    If you have specific questions or use cases, I'm happy to share what I know and I think I can say the same for Joe.

                     

                    Jonathan

                    • 7. Re: finding number of days between two dates
                      SUK Rudge

                      Hi Joshua

                      Thank you for the immeadiate reply.i am actually looking for this kind of format just take a look at of my attachment.I have patient fall date and number of falls.now i have to map number of days between each fall using the fall date and no of falls on the chart to create G chart.

                      I want to know how to calculate no of days between each fall using the fall date?

                       

                      Pls advice me in this.

                      • 8. Re: finding number of days between two dates
                        Jonathan Drummey

                        See the attached, I used INT() instead of datediff, but it gets the same results as Joshua's calc. Note that I fixed the Compute Using of the table calc to the Fall ID, that way if you drag another pill into the view (such as a floor/unit, to generate a small multiples chart) the calculation will occur separately for each unit. The average line was generated using a reference line.

                         

                        Jonathan

                        • 9. Re: finding number of days between two dates
                          SUK Rudge

                          Thank you so much Joshua and Jonathan for helping me in this

                          • 11. Re: finding number of days between two dates
                            Pat Grady

                            This is wonderful, thanks for sharing!

                             

                            Follow-up question:

                             

                            Once you have a coumn full of the "difference of days from previous", what would be the best way to find the average of that column?

                            • 12. Re: finding number of days between two dates
                              Alex Cook

                              Second follow-up question for the group:

                               

                              I want to count the days between two dates, but I want the count to be based on the first opportunity for each account, and to reset with each new account - see attached. There can be multiple opportunities per account. I used Josh/Jonathan's suggestion to set up a Date Diff field, but it does not reset - it always just looks to the previous line to calculate the datediff (due to the "-1" in the lookup). I tried setting up a "Date of First Opp" field with the intent of just doing a datediff b/w this field and the Date field, but it is not dynamic. How do I get it to look back a dynamic number of lines? Anyone have any suggestions? This is easy to do in Excel but not so much in Tableau (or at least I don't know how to do it).

                               

                              Thanks,

                              Alex

                              • 13. Re: finding number of days between two dates
                                Matt Lutton

                                Is this the result you're after?

                                 

                                Result DateDiff.png

                                • 14. Re: finding number of days between two dates
                                  Alex Cook

                                  Thanks Matthew - Almost, but not quite. I figured the answer was somewhere

                                  in the Table Calculation window...my mistake was I was only dragging

                                  Account into the Addressing window so the "At the level" and "Restarting

                                  every" options were not enabled. I needed to drag all the fields over to

                                  the Addressing window. But, this is still incorrect since while the count

                                  resets for every account, within each account it is still referencing the

                                  prior opportunity. For example, for Account A, the correct datediff for Opp

                                  3 would be 31 (the number of days b/w 1/1 and 1/31), not 17 as it's

                                  currently showing.

                                   

                                  I figured out the correct result using a slightly different method (see

                                  Method 2 tab), but it would still be helpful to know how to do this using

                                  my first method.

                                   

                                  Alex

                                   

                                  On Thu, Nov 6, 2014 at 11:37 AM, Matthew Lutton <

                                  1 2 Previous Next