-
1. Re: Gantt Chart - calculate occurences between date periods
swaroop.gantela Jun 20, 2016 6:54 PM (in response to paolo.raia)1 of 1 people found this helpfulPaolo,
Welcome to the Forums.
kettan's excellent post may prove useful for
doing the counts and aggregations.
Also Here is an older thread that looks similar:
-
2. Re: Gantt Chart - calculate occurences between date periods
paolo.raia Jun 23, 2016 6:24 AM (in response to swaroop.gantela)Many thanks for your reply. Helped me immensely.
-
3. Re: Gantt Chart - calculate occurences between date periods
paolo.raia Jun 29, 2016 9:27 AM (in response to paolo.raia)Hi again,
I require some further guidance on this please. Below, is a simplistic gantt chart:-
I've applied the rules from the links on this thread, but still have an issue.
For example, case 'C' has a start date of 2-Jan and end date of 6-Jan. In my criteria fields, if I enter a start date of 3-Jan and end date of 6-Jan, 'C' drops out. I still need it to show as the dates in between still fall within the criteria.
I'm using ...
[CREATED DATE]>=[STARTDATEPARAM]
AND
[COMPLETED DATE]<=[ENDDATEPARAM]
And I have [CREATED DATE] on the columns shelf, which may be where I am going wrong. But if so, what do I need to place there instead? It's almost as if I need a further calculation to calculate the days in between the start and end date.
Thanks for listening.
Regards.
-
4. Re: Gantt Chart - calculate occurences between date periods
swaroop.gantela Jun 29, 2016 9:44 AM (in response to paolo.raia)Paolo,
Firstly, I apologize, I forgot to add kettan 's link:
CROSS JOIN with Tableau's join dialog
Sorry about that.
Will look now at your latest.
-
5. Re: Gantt Chart - calculate occurences between date periods
swaroop.gantela Jun 29, 2016 10:07 AM (in response to paolo.raia)Paolo,
Please see if the attached may be useful for you.
The datasource has one sheet with the start and end dates for each case,
and a second sheet with all the possible dates (lookupDates). They are joined together by a key of 1.
Then you can use a filter like this to get just the dates between:
[Look Up Date]>=[Start Date]
AND [Look Up Date]<=[End Date]
Then you can use the LookUpDate on the Column Shelf to make charts
and to aggregate and get counts over time. Also you can use the LookUpDate
as a filter for Start and End periods of your view.
-
209386gantt.twbx 31.2 KB
-
209386gantt.xlsx 9.9 KB
-
-
6. Re: Gantt Chart - calculate occurences between date periods
paolo.raia Jun 30, 2016 8:18 AM (in response to swaroop.gantela)Hey Swaroop,
Thank you ever so much for your examples and links. Very helpful indeed and I am currently working your ideas into my project.
Can I please ask one further question if I may?
I have seen the LookUp join that you have created within the same data source. However, my join will need to be between an Excel file and a SQL table. Is this achievable and, if so, how?
Many thanks and regards.
-
7. Re: Gantt Chart - calculate occurences between date periods
swaroop.gantela Jun 30, 2016 9:03 AM (in response to paolo.raia)Paolo,
In general, I don't think there should be a problem with blending your two sources:
Blending SQL Server with Excel / two different data types.
From where do your start and end dates come from, are the both from the same source?
I think the date LookUp is external to those sources.
It is just a single column listing of all possible dates, which go as far back or forward as possible.
-
8. Re: Gantt Chart - calculate occurences between date periods
paolo.raia Jun 30, 2016 9:37 AM (in response to swaroop.gantela)Hi Swaroop,
The Case No, Start Date and Completed Date come from a SQL table. I have created a LookUp in an Excel file for a 5 year date period. I cannot add a key to the SQL table so picked a field which has constant data and used this as a key in Excel.
Do I just use EDIT RELATIONSHIOP for this and join the fields that way?
Thank you.
-
9. Re: Gantt Chart - calculate occurences between date periods
swaroop.gantela Jun 30, 2016 10:42 AM (in response to paolo.raia)Paolo,
Yes, any common key should work,
just want to get all the combinations and then filter out
to only the pertinent ones.
The Edit Relationships should work.
-
10. Re: Gantt Chart - calculate occurences between date periods
paolo.raia Jul 1, 2016 4:18 AM (in response to swaroop.gantela)Hi Swaroop,
Many thanks for your help on this. I have managed to replicate your example and it works very well indeed.
However, I can only get this to work using a join from the same workbook. if I am trying to join via the EDIT RELATIONSHIP option (Excel to a SQL Datamart) it doesn't seem to blend as it should. Maybe I'm missing something.
Thanks again.