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

# Calculate # of days from the date range field

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

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

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

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

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

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

So here is a sample data set

 ScheduleDate Total Hours Cases checked TechID Average Cases Checked per day 9/17/2018 9.1 39.0 1 Cases Checked/ Days worked 9/21/2018 9.5 40.0 1 9/24/2018 9.0 43.0 1 9/27/2018 6.7 31.0 1 9/28/2018 -0.2 1.0 1 9/17/2018 8.7 12.0 2 9/18/2018 10.6 20.0 2 9/19/2018 8.5 11.0 2 9/20/2018 8.2 9.0 2 9/21/2018 8.5 27.0 2 9/24/2018 2.7 26.0 2 9/25/2018 8.9 31.0 2 9/26/2018 12.4 42.0 2 9/27/2018 7.8 12.0 2 9/28/2018 5.0 2 9/17/2018 11.1 35.0 3 9/18/2018 7.5 22.0 3 9/19/2018 8.5 28.0 3 9/20/2018 8.0 21.0 3 9/21/2018 8.5 17.0 3 9/24/2018 9.6 23.0 3 9/25/2018 11.9 48.0 3 9/26/2018 10.1 33.0 3 9/27/2018 9.7 37.0 3 9/28/2018 -0.6 67.0 3 9/17/2018 6.0 4 9/18/2018 8.6 12.0 4 9/19/2018 11.8 30.0 4 9/20/2018 8.2 42.0 4 9/21/2018 8.5 40.0 4 9/24/2018 9.1 28.0 4 9/25/2018 7.0 21.0 4 9/26/2018 10.7 39.0 4 9/27/2018 11.2 38.0 4 9/28/2018 -1.0 62.0 4 9/17/2018 8.7 49.0 5 9/18/2018 8.3 48.0 5 9/19/2018 8.4 59.0 5 9/20/2018 8.1 47.0 5 9/21/2018 10.8 58.0 5 9/24/2018 8.8 45.0 5 9/25/2018 8.8 37.0 5 9/26/2018 8.7 58.0 5 9/27/2018 8.6 58.0 5 9/28/2018 -0.5 89.0 5 9/17/2018 8.5 33.0 6 9/18/2018 8.1 48.0 6 9/19/2018 8.6 57.0 6 9/20/2018 11.1 61.0 6 9/21/2018 8.8 36.0 6 9/24/2018 5.0 6 9/25/2018 9.0 39.0 6 9/26/2018 8.3 51.0 6 9/27/2018 9.3 50.0 6 9/28/2018 -0.2 122.0 6 9/17/2018 12.0 7 9/18/2018 20.0 7 9/19/2018 14.0 7 9/20/2018 10.0 7 9/21/2018 5.0 7 9/24/2018 16.0 7 9/25/2018 18.0 7 9/26/2018 3.0 7 9/27/2018 1.0 7 9/28/2018 2.0 7 9/17/2018 10.8 28.0 8 9/18/2018 8.8 24.0 8 9/19/2018 9.3 51.0 8 9/20/2018 9.2 31.0 8 9/21/2018 10.3 39.0 8 9/24/2018 11.3 45.0 8 9/25/2018 9.7 31.0 8 9/26/2018 8.9 46.0 8 9/27/2018 9.1 26.0 8 9/28/2018 -0.8 55.0 8 9/17/2018 7.6 12.0 9 9/18/2018 7.8 7.0 9 9/19/2018 7.6 9.0 9 9/20/2018 7.2 12.0 9 9/21/2018 6.8 14.0 9 9/24/2018 7.4 16.0 9 9/25/2018 7.9 9.0 9 9/26/2018 5.8 10.0 9 9/27/2018 7.4 18.0 9 9/28/2018 -1.2 56.0 9
• ###### 7. Re: Calculate # of days from the date range field

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

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:

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

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