Thanks for feedback.
I have tested on my side and it is working well !
Glad to hear!
(I didn't open your twbx because of it was not possible with my version, i have rebuilt it).
I am having the same issues. I can't open any attached workbook because I only have an expired 9.0 license. I used Tableau Public to do the attached which means I can't create version 10.2 (which I assume you use).
Do you think it is possible to to the same with the possibility to zoom in the year.
I mean with a common time hierarchy (YEAR, QUARTER, MONTH, DAY).
I have around 250 000 lines in my table
I use a bigquerry connection.
It is the first time i use it but it seems to be powerfull.
It is for sure possible, but may not be recommendable. Looking at the estimates for each time (see at the bottom), I wouldn't split contract periods into DAYs. You could of course try this out, but maybe the best thing is to use the UNION method for your data. I might take a look at this (union).
On the other hand, I think (but not sure though) that Tableau with a live connection will only return whatever is needed for the view (does a group by). Therefore this method might actually still perform well because a powerful database is doing the hard work.
The formulas to split period into reporting YEAR, QUARTER, MONTH, DAY:
Reporting Filter YEAR : [Period] <= DATEDIFF('year',[Start Date],[End Date]) QUARTER : [Period] <= DATEDIFF('quarter',[Start Date],[End Date]) MONTH : [Period] <= DATEDIFF('month',[Start Date],[End Date]) DAY : [Period] <= DATEDIFF('day',[Start Date],[End Date]) UNION ; [Period] <= 2
See a flexible reporting time solution with your dummy data here: Re: Calculation depending on a Start Date and a End Date and represent it on a time axis
Attached workbook version: 9.0 (which I assume you can open)
2 of 2 people found this helpful
v2 TIME FLEXIBLE
Attached (10.3, sorry) is a new version of the same solution that is flexible with time.
OBS! A BETTER ALTERNATIVE!?
A solution that doesn't split periods into reporting units is most likely better for 250000+ contracts having hundreds of millions of "contract days" as already mentioned in Re: Calculation depending on a Start Date and a End Date and represent it on a time axis.
Showing Records That Fall Within a Period of Time | Tableau Software looks like an attractive method because underlying 250000 contracts would be left unchanged as 250000 records. I am curious to hear how well this works for you (and others). Here are threads where it is provided as correct answer:
Answer Provided By To Question 2017-04-19 Simon Runc Amount of tickets every day 2017-04-22 Simon Runc How to create calculated field for aggregated dates in a date range
This is the search that found these: https://community.tableau.com/search?q=%22Showing+Records+That+Fall+Within+a+Period+of+Time%22&sort=updatedDesc
THE ATTACHED WORKBOOK
It uses The Period Split Into Data Rows Method.
More rows are added to the sheet Period so it also supports days.
DUMMY JOIN (1=1)
It is the same as before. No change.
PARAMETER [Split As]
This parameter is used by the calculations [Filter (Split)] and [Reporting Time].
CASE [Split As]
WHEN 1 THEN DATEDIFF('year',[Start Date],[End Date])
WHEN 4 THEN DATEDIFF('quarter',[Start Date],[End Date])
WHEN 12 THEN DATEDIFF('month',[Start Date],[End Date])
WHEN 365 THEN DATEDIFF('day',[Start Date],[End Date])
CASE [Split As]
WHEN 1 THEN DATE(DATETRUNC('year',DATEADD('year',[Period],[Start Date])))
WHEN 4 THEN DATE(DATETRUNC('quarter',DATEADD('quarter',[Period],[Start Date])))
WHEN 12 THEN DATE(DATETRUNC('month',DATEADD('month',[Period],[Start Date])))
WHEN 365 THEN DATE(DATETRUNC('day',DATEADD('day',[Period],[Start Date])))
Attached Workbook Version: 10.3
Sorry for delay, i was too much busy on others tableau desktop subjects.
Could you share me your new TWBX witth the good version ?
I ma not able to dot it with mine
What version of Tableau do you use?
Hi Kettan in version 10.2
It might be possible to hack the twb file from a text editor by replacing version number 10.3 with 10.2. Since this hack probably is shared now and then, I search for the keywords twb+hack and found this by Joe Oppelt : Re: Convert .twb in 10.3 to 10.2 version. According to talk back and forth in that thread, it seems that this hack doesn't work anymore. Despite of this, I have changed the attached workbook to 10.2 with the hope that it might work. If it doesn't, then we at least know that this is not an option.
See how the attached workbook was changed from version 10.3 to 10.2 in gif below:
If this didn't work, you could install version 10.3 and thereafter re-install 10.2 on top of it to make 10.2 the default. I think this technique has been shared in the forum before but couldn't find it. With a Google search, I found this official documentation from Tableau:
According to option 1 in this document, you might not need to re-install 10.2.