10 Replies Latest reply on Jul 1, 2016 4:18 AM by paolo.raia

# Gantt Chart - calculate occurences between date periods

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?

• ###### 1. Re: Gantt Chart - calculate occurences between date periods

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:

Histogram of concurrent activities

1 of 1 people found this helpful

• ###### 3. Re: Gantt Chart - calculate occurences between date periods

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

Paolo,

Will look now at your latest.

• ###### 5. Re: Gantt Chart - calculate occurences between date periods

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.

• ###### 6. Re: Gantt Chart - calculate occurences between date periods

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.

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

Paolo,

In general, I don't think there should be a problem with blending your two sources:

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

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

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

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.