14 Replies Latest reply on Jan 19, 2020 11:41 PM by Richard Sanchez

    Help with counting between two dates

    Richard Sanchez

      Hello Tableau gurus,

       

      I need to count active patients between 2 dates for a period of three years. I only have a start and an end date. If I create a calculated field for every year it works with next three formulas. But then I need three different bar charts to get what I want.

       

      calculated field 1:

      calculated field 2:

       

      calculated 3:

      Result with three separate bar charts on a dashboard:

       

      I now want to combine those three calculated fields into one so I can use them in a singe bar chart so I can work more flexible with it. I came up with this combined formula:

       

       

      Now the result is:

       

      Ofcourse I can filter out the Null values but if you look closer, only the first year is correct. The other two don't match so something in my combined formula is wrong. Can anyone see my mistake?

       

      Kind regards,

      Richard

        • 1. Re: Help with counting between two dates
          swaroop.gantela

          Richard,

           

          My apologies, I didn't read through your post very carefully,

          but first just wanted to note that a standard approach

          to counting when given start and end dates is to use a date scaffold:

          Creating a Date Scaffold in Tableau - The Flerlage Twins: Analytics, Data Visualization, and Tableau

           

          Though it may make your dataset larger, it does provide quite a bit

          of capability when grouping by year or showing daily patient counts, etc.

           

          If that is not feasible for your data, we can revisit.

          • 2. Re: Help with counting between two dates
            Jim Dehner

            good morning

             

            this is a variation of the 2 date problem - it feels like there is a single calendar for the based on date but the data are actual aligned with 2 separate calendars - one start the other end

             

            you want the data aligned against a single calendar and one way to do that is with a date scaffold

            it not difficult - just a few steps - I keep a solution on my blog with a downloadable How to solution on my public site - see  See it your way: Scaffold

             

            if looks like this

             

            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.

            • 3. Re: Help with counting between two dates
              shruten parmar

              Hi RICHARD,

               

              Looked into your formula. It's looks fine.

               

              Can you share a workbook with twbx format with some sample data?

              Without looking into workbook it's difficult to say.

               

               

              Thanks,

              Shruten

              • 4. Re: Help with counting between two dates
                Richard Sanchez

                Hi Jim,

                 

                I think your solution will work for me but the dataset is getting very big. I've tested it with 4 rows and after joining it with a scaffold date calendar of 3 years (3x365 days) the test dataset now has 4384 rows so it's becoming more than 1000 times bigger. My production dataset has 9200 rows and after doing this trick it it now has about 10 milion rows (also 10 times bigger).

                 

                Last 3 questions:

                 

                1. In my own solution I use a count distinct on the customer id to count my customers per year/month. Does your query return only unique values? In your example you mention Account ID and those numbers look unique in your dataset but in my dataset patients come and go so in a period of 3 years I have to count distinct the customer id per year/month. I find LOD calcs hard to understand.

                 

                2. How can I change the MDY approach you use to a Year or Quarter or Month?

                 

                3. Is it possible to create a scaffolf date calender that only contains months or years instead of on a daily basis and join it? I've tried to create one with only 3 values: 2017, 2018, 2019. The formula seems to work but if I look closer it does not give the right values. For example, if a patients comes in on the 5th of January 2018 and the formula checks  if min([datumveld])>=min([Start datum]) then that patient does not come up because Tableau makes it 1-1-2018 instead of 2018 so my scaffold date is smaller than the formula wants. Change it to datepart was the next thing I thought of but then the syntax does not accept the formula. Any ideas?

                 

                 

                 

                Kind regards

                Richard

                • 5. Re: Help with counting between two dates
                  Jim Dehner

                  Hi Richard - the solution I gave you is focused on getting the data structure in a form that has a continuous date calendar and places your data against the calendar - once in that form you can make any number of calculations against the data - by looking at each date individually -

                  if you want to look at the month/year level just set the scaffold dates to the month and year in a date form - I would use the first day of the month like 1/1/2020   and then in the formulas to count patients the date comparison would be     datetrunc('month',(date))   which returns the first day of the month of date

                   

                  the min goes inside the parents    like year(min(scaffold date))   

                   

                   

                  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: Help with counting between two dates
                    Richard Sanchez

                    Hi Jim,

                     

                    Thx for your reply. So a scaffold calendar only works when you create it with all days of a year so there is no escape in blowing the data set up a thousand times (3 years). 

                     

                    When I change MDY to Year or Month the staffold calc that sums up the patients explodes. Year 2018 only has to show 3 dummy patients and year 2019 has to show 4 patients.

                    I've attached a small dummy workbook with fake data and hope you can show me the way because I still can't figure it out. Thanks in advance!

                     

                    Kind regards, Richard

                     

                    Dummy dataset in the workbook:

                     

                    Viz on Tableau public

                     

                    https://public.tableau.com/static/images/du/dummyworkbook/Dashboard/1.png

                    • 7. Re: Help with counting between two dates
                      Jim Dehner

                      Richard - did you forget to attach the workbook?

                       

                      Jim

                      • 8. Re: Help with counting between two dates
                        Richard Sanchez

                        I published it to Tableau Public because I don't know how to attach it:

                         

                         

                        Tableau Public

                        • 9. Re: Help with counting between two dates
                          Jim Dehner

                          I can not download the workbook

                          you can attach the workbook as follows

                           

                          first click on reply in the lower right corner of the post

                           

                           

                          when the new window opens click here in the upper right corner

                          another window will open click in the lower right and

                           

                          and you file manager will open so you can select the file to post

                           

                          MAKE SURE IT IS A TWBX FILE WITH THE DATA EXTRACTED

                           

                           

                          jim

                          • 10. Re: Help with counting between two dates
                            Richard Sanchez

                            Hi Jim,

                             

                            Thank you. I've attached it.

                            • 11. Re: Help with counting between two dates
                              Jim Dehner

                              if you are content with month level totals ( I think that is what you said in the post

                              then the scaffold can be at that level

                               

                               

                              I replaced your scaffold like this

                               

                              and the count is simply this

                               

                               

                               

                               

                              and it will return this

                               

                              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.

                              • 12. Re: Help with counting between two dates
                                Richard Sanchez

                                Hi Jim,

                                 

                                Awesome! Only one tiny thing left  ;-). In the dummy set id2 patient is active between 15-1-2018 - 15-4-2019 but in the line chart januari shows 0?

                                 

                                • 13. Re: Help with counting between two dates
                                  Jim Dehner

                                  right you are

                                  I had the end conditions wrong - this will correct that -

                                   

                                   

                                   

                                  it puts ID 2 start back in January

                                   

                                   

                                  JIm

                                  • 14. Re: Help with counting between two dates
                                    Richard Sanchez

                                    Thank you Jim! Everything now works as needed!

                                     

                                    Regards,

                                    Richard