9 Replies Latest reply on Oct 2, 2018 4:29 AM by Daniel Stanish

    Calculate # of days from the date range field

    Thanushree Sampangi Pushpa

      if you have a date dimension and use it in  the filter as a data range say from 2018/09/24 -- 2018/09/27, the number of days in this date range is 4.

       

      How to calculate this using a calculated field?

       

      When I researched I only answer I found were concentrated on calculating the # of days between two different dates using DateDiff() function

        • 1. Re: Calculate # of days from the date range field
          Daniel Stanish

          You could try using the DateDiff function against the MIN and MAX of the date you are filtering on. Is that what you're looking for, the range of the filter?

           

               DATEDIFF('day',MIN([theDate]),MAX([theDate])

           

          Another possible alternative is to:

          • Create two parameters "Start Date" and "End Date"
          • Use the DateDiff against the parameter fields to give you the range:
            • DATEDIFF('day',[Start Date],[End Date])
          • Create a calculated field to use as a filter and add it to the filters pane:
            • [Start Date] <= [theDate] AND [theDate] <= [End Date]
          1 of 1 people found this helpful
          • 2. Re: Calculate # of days from the date range field
            Thanushree Sampangi Pushpa

            Thanks for the swift reply Daniel.

             

            I tried MIN MAX approach, for some reasons I'm only getting 4 days instead of 5 for a date range 09/17 - 09/21

             

            With the Alternative approach I dont seem to get any values at all.

             

            Here is what I used    [Start Date]<= [Date]  AND [Date]<= [End Date]  it returns TRUE,FALSE & NULL

            • 3. Re: Calculate # of days from the date range field
              Vinnie Ahuja

              If I understand correctly, it seems what you're trying to do will systemically differ from the approach Daniel suggests by 1.  Can you not do the following then?

               

              DATEDIFF('day',MIN([theDate]),MAX([theDate]) +1

              1 of 1 people found this helpful
              • 4. Re: Calculate # of days from the date range field
                Thanushree Sampangi Pushpa

                Well, this approach did work. Thanks Ahuja.

                 

                But I don't have any values for the weekends so if my date range is 2018/09/17 - 2018/09/23 it counts as 5 which is right according to the number of working days(but the actual number of days in this range is 7). But if my date range is 2018/09/17- 2018/09/24 it counts the weekends so I end up with 8 . I'm not quite sure of what is happening here.

                • 5. Re: Calculate # of days from the date range field
                  Vinnie Ahuja

                  It's hard to diagnose without a better picture of what specifically you're doing and what your viz looks like.  I would expect this calculation to count the weekends, so the second scenario you describe seems correct.  I am not sure about the first.

                  • 6. Re: Calculate # of days from the date range field
                    Thanushree Sampangi Pushpa

                    So here is a sample data set

                     

                     

                        

                    ScheduleDateTotal HoursCases checkedTechIDAverage Cases Checked per day
                    9/17/20189.139.01Cases Checked/ Days worked
                    9/21/20189.540.01
                    9/24/20189.043.01
                    9/27/20186.731.01
                    9/28/2018-0.21.01
                    9/17/20188.712.02
                    9/18/201810.620.02
                    9/19/20188.511.02
                    9/20/20188.29.02
                    9/21/20188.527.02
                    9/24/20182.726.02
                    9/25/20188.931.02
                    9/26/201812.442.02
                    9/27/20187.812.02
                    9/28/20185.02
                    9/17/201811.135.03
                    9/18/20187.522.03
                    9/19/20188.528.03
                    9/20/20188.021.03
                    9/21/20188.517.03
                    9/24/20189.623.03
                    9/25/201811.948.03
                    9/26/201810.133.03
                    9/27/20189.737.03
                    9/28/2018-0.667.03
                    9/17/20186.04
                    9/18/20188.612.04
                    9/19/201811.830.04
                    9/20/20188.242.04
                    9/21/20188.540.04
                    9/24/20189.128.04
                    9/25/20187.021.04
                    9/26/201810.739.04
                    9/27/201811.238.04
                    9/28/2018-1.062.04
                    9/17/20188.749.05
                    9/18/20188.348.05
                    9/19/20188.459.05
                    9/20/20188.147.05
                    9/21/201810.858.05
                    9/24/20188.845.05
                    9/25/20188.837.05
                    9/26/20188.758.05
                    9/27/20188.658.05
                    9/28/2018-0.589.05
                    9/17/20188.533.06
                    9/18/20188.148.06
                    9/19/20188.657.06
                    9/20/201811.161.06
                    9/21/20188.836.06
                    9/24/20185.06
                    9/25/20189.039.06
                    9/26/20188.351.06
                    9/27/20189.350.06
                    9/28/2018-0.2122.06
                    9/17/201812.07
                    9/18/201820.07
                    9/19/201814.07
                    9/20/201810.07
                    9/21/20185.07
                    9/24/201816.07
                    9/25/201818.07
                    9/26/20183.07
                    9/27/20181.07
                    9/28/20182.07
                    9/17/201810.828.08
                    9/18/20188.824.08
                    9/19/20189.351.08
                    9/20/20189.231.08
                    9/21/201810.339.08
                    9/24/201811.345.08
                    9/25/20189.731.08
                    9/26/20188.946.08
                    9/27/20189.126.08
                    9/28/2018-0.855.08
                    9/17/20187.612.09
                    9/18/20187.87.09
                    9/19/20187.69.09
                    9/20/20187.212.09
                    9/21/20186.814.09
                    9/24/20187.416.09
                    9/25/20187.99.09
                    9/26/20185.810.09
                    9/27/20187.418.09
                    9/28/2018-1.256.09
                    • 7. Re: Calculate # of days from the date range field
                      Vinnie Ahuja

                      If its ScheduleDate that you're using in the calculation, I think I see what is going on.  You'll note that there is no record with a date of 9/23 or 9/22 for that matter.  Thus when you set the range to 9/17 - 9/23, it is actually only counting to 9/21, which gives you 4 days +1 = 5.  When you set it to 9/17 - 9/24, it finds records on 9/24, the last day of the range and works as expected.

                      • 8. Re: Calculate # of days from the date range field
                        Vinnie Ahuja

                        If you need to consider dates that may not have any records in ScheduleDate, you may benefit from an "independent calendar" that contains the entire range of possible values.  You could pull one in (in an Excel file or something) and left join your data source to this calendar using ScheduleDate.  This is a very common situation that comes up.  You'll be able to find many examples of this in these Forums.  Here are couple examples:

                         

                        Universal date field

                        Date Range List

                        • 9. Re: Calculate # of days from the date range field
                          Daniel Stanish

                          To add onto your answer, you can generate a sequential list of dates if you have access to a database server. In Oracle, the SQL for generating a date list for the past year is:

                           

                          SELECT

                               ADD_MONTHS(SYSDATE,-12) + rownum - 1 the_date

                          FROM   

                               DUAL

                          CONNECT BY ADD_MONTHS(SYSDATE,-12)  + rownum - 1 <= SYSDATE

                           

                          -Dan