DATEDIFF('day', [Start Date], [End Date])
You'll get the diff between the two dates in an integer value of days.
Are you saying that you actually need to list the specific number of days per each month?
Tableau works on actual rows of data. If you don't have a row on each date in each month that the contract was in effect, you won't have anything physical to count up. In fact, if you even had one row per month you could hack up a series of calcs to compute the number of days for each month, but if you just have one row per contract to give the start- and end-date, you'll have a hard time getting what you're looking for.
The date diff doesn't work because I need to know the number of days per month.
If you look at my example it shows the total for unique_ID 9.
1) What I should see across the top would be each month displayed from July 2017 to May 2018.
2) Under each month from July 2017, I should see 31 days August 31 days, Sept. 30 days, etc.
3) I would then total the row and Columns.
What I am thinking which I haven't been able to write a formula for is as follows:
IF StartDate is Column month then datediff from end of month to Start days
Else if StartDate month is Less than current month then days for month
Else If endDate Month <> Current Month the Days for month;
Else if end date is current month then days to end date.
I am not sure if this explains it.
Here is an example of what I hope my report will look like.
Sample_Finished_Report.xlsx 12.2 KB
So, I was thinking that I will need to compare to days of the month.
2 of 2 people found this helpful
What you're looking to do will involve a process known as scaffolding (a support system) for your date spans. Please see attached 10.3 workbook and below screenshots for how this is done. It's fairly simple after joining a new table of data which is nothing more than a set of continuous dates from 01/01/2017-12/31/2018:
If this resolves your question, please mark as correct. Thx! Don
I brought in a new MS-Excel worksheet which is just a series of consecutive dates for a two-year period:
Then did a Cartesian JOIN 1:1, using the very bottom of the drop down and simply type 1 on each side of the join, selecting INNER JOIN:
Notice the conflation of the data. This procedure works really well for small sets of time, but may cause a great deal of expansion if using larger sets of data/time:
There's a new calculation which is a T or F filter brought into the Filter's card:
[Calendar Date] >=[Start Date]
[Calendar Date] <=[End Date]
I then swapped out the Start Date from Columns with Calendar Date, then swapped out SUM of Contract Days with COUNT(Unique ID), then added grand totals for rows and for columns.
1 of 1 people found this helpful
I did a little bit different than Don Wise BTW Don, where are the contracts without end dates?
Indeed, we need master data, that is what Joe Oppelt mentioned. The idea is to have for every contract the dates where it is valid, for example Unique ID=9 we must have rows from 01/07/17 to 08/02/18 = 8 rows
I did the same as Don - added master data to your excel with dates until 2023. If you know SQL and data is in DB then it master data generation can be done with a query.
Then I did the RIGHT JOIN calculation:
date(datetrunc('month',[Start Date])) <= Master Date
And then I did the viz:
- Filtered out rows where Unique ID is null - rows from master data before min Start Date
- Filtered out extra records for months after today() month. Just to make viz smaller and faster
- Found the marks where End Date = Master Date (red color) just to see where it was ended. Orange is for Unique IDs with end date, blue (null) - Unique ID has not yet ended
- Found the end of each Master Month - Max Day Month.
We will need it to calculate number of days for month where Unique ID is valid
- Calculated duration in days - Duration
- If Unique ID has 2 contracts then master dates will be duplicated, so I want to filter out them
- If Start Date and End Date is in the same month - the duration is between Start Date and End Date
- If id is valid then duration is from Start Date of to end of master for first month and
- from the start of master to the end of master. The same logic if End Date is null
Hope that works! At least I've enjoyed solving it
- Some Unique IDs are not really Unique
Thank you that is 1/2 of the puzzle. That is picking up everyone that has an end date. I also need the number of days for the group that haven't terminated the contact.
So, I modified your date filter to include this and It looks correct. .
[Calendar Date] >=[Start Date]
([Calendar Date] <=[End Date]
Or IF (ISNULL([End Date])) THEN