Hi there.
First post and pretty much a newbie!
I have created a simple gantt chart (below) and am trying to calculate the number of occurrences between each date period:-
So for example, the yellow "wonky" line is showing 5 occurrences for the period 26MAR to 10APR. How can I calculate this and even transpose the results to a crosstab?
Many thanks in advance.
Paolo,
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:
Many thanks for your reply. Helped me immensely.
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.
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.
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.
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.
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.
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.
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.
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.