7 Replies Latest reply on Aug 30, 2017 1:16 PM by Jim Dehner

    Showing record that fall within a period of time - troubleshooting

    Kiley Bijlani

      Hi,

      I am working on a vis to show open episodes over time. I found a great thread on this FAQ:  Open & Close Dates  and was able to develop a vis using this solution:

      Showing Records That Fall Within a Period of Time | Tableau Software

      The problem I am having is that this solution does not count partial weeks. For example if a client starts on 12/31/2016 and ends on 2/3/2017, I would like to see an open episode/contract for the weeks of :

      12/25/2016

      1/1/2017

      1/8/2017

      1/15/2017

      1/22/2017

      1/29/2017

      2/5/2017

      Instead the vis only shows an open episode on the full weeks:

      1/1/2017

      1/8/2017

      1/15/2017

      1/22/2017

      1/29/2017

      By removing the -1 from running date I can get the open episode/contract to start on the right date but it ends 2 weeks early.  I'm pretty new to Tableau and am having trouble figuring out how to expand the selection to include the partial weeks.

      I have been using the sample data set from Showing Records That Fall Within a Period of Time | Tableau Software  with the following modifications to test:

       

      ClientDateClosed Date
      A6/1/20169/5/2016
      B7/24/20164/2/2017
      C5/1/201610/20/2016
      D12/31/20162/3/2017
      E11/21/20166/2/2017
      F5/1/20168/12/2017
      G2/5/2017
      H8/15/2017

      I also added a calculated date field that put's in Todays date +1 for null closed dates, so currently open episodes/contracts are counted.

      Thanks for your help!

      Kiley

        • 1. Re: Showing record that fall within a period of time - troubleshooting
          Jim Dehner

          Kiley

          Please attache a workbook with data in a twbx format so we can see your calculations and how the dat is structured in your data source

          Thanks

          Jim

          • 2. Re: Showing record that fall within a period of time - troubleshooting
            Kiley Bijlani

            Hi Jim,

            I have not attached a workbook before, so let me know if this didn't work.

            Thanks!

            Kiley

            • 3. Re: Showing record that fall within a period of time - troubleshooting
              Jim Dehner

              OK

              This war really nasty but I got it give a result that looks like this

               

               

              I found a couple of things = the first is you created a calculated field for Index()-1 but when you dropped it into the running formula the automatic measure recognition dropped in Index so your formula did this

              DATEADD('week',(INDEX()), [Start date])  versus this

               

              DATEADD('week',(INDEX()-1), [Start date])

               

               

              the start date should be DATETRUNC('week', window_min((MIN([Date]))))  (I can't remember if i had to change that

               

              OK and the second thin is that your period went over the end/start of the year - the model you followed was all contained in a single year

              the viz you created put Week(date) on columns but you needed both Year(date) and week(date) on the columns

               

              By just using week it aggregated all the same number weeks together regardless of year

               

              here it is as a bar chart

               

              It was pretty difficult to get the table calculations correct

              Here is a screen shot of each

               

               

              That should do it

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Showing record that fall within a period of time - troubleshooting
                Kiley Bijlani

                Hi Jim,

                Can you attach your workbook?  I am not getting the same result.  The index fields were not meant to be part of the calculation, I was just trying to understand what the index calculation does. I should have deleted them to eliminate confusion! Here's what I get now:

                It doesn't seem to be capturing the weeks correctly, you can see this best in the last few weeks of July and August.

                Thanks for looking at this,

                Kiley

                • 5. Re: Showing record that fall within a period of time - troubleshooting
                  Jim Dehner

                  hi I will attach the workbook - it is in t10.3 so you may not be able to open it

                  see the screen shot below - yes I see the difference - can't say without looking at the data but since all the other points match I woud look hard at the data and any filters that might be applied

                   

                   

                   

                   

                   

                  Jim

                   

                   

                  If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                  • 6. Re: Showing record that fall within a period of time - troubleshooting
                    Jim Dehner

                    Hi

                    I spent some time on Saturday and yo are right - for some reason - yet to be resolved - there is a 4 week difference at the start of 2017 - I am looking into it but have not yet found a solution

                     

                    I will let you know

                    JIm

                    • 7. Re: Showing record that fall within a period of time - troubleshooting
                      Jim Dehner

                      Hi

                      I'm sure you thought that I gave up on this - don't blame you - but actually I have been working on it and researching the issue

                       

                      This was really confusing because first you cross over a year end and second because there are 2 indexes involved - one to increment the running date and a second one that triggers the totaling of open active clients

                       

                      I think this is right - I tried to go through the data but no guarantees

                       

                       

                      I kept getting the dates wrong - particularly in 2017 but the problem started on day one -

                      The formula for start date JD - DATETRUNC('week', window_min((MIN([Date]))))  - is directly from the example you were working from

                      The running date is           DATEADD('week',([INDEX() jd]-1), [Start date JD])

                       

                      Where the indexJD is calculated as shown

                       

                      Then the running date is      DATEADD('week',([INDEX() jd]-1), [Start date JD])

                      and is set up as

                       

                      and the is-active is 

                                IF [running date jd] >= WINDOW_MAX(MAX([Date])) AND

                                [running date jd]<= WINDOW_MAX(MAX([closed date no nulls]))

                                Then 1 Else 0 END

                      and is set up as

                       

                       

                      Now the # of actives is           IF INDEX()=1 THEN WINDOW_SUM([isActive]) END  -- but this is not the same index -

                       

                       

                       

                      and yes I know the plate says it is linked to Index() JD but the the trigger in the formula for it to calculate is just Index()

                       

                      Sorry this took so long - there was a lot going on with ordinal calculations that made it difficult to see what was going on

                       

                      Jim

                      If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.